Multiple Button to add in Excel

Question

I wanted to add Button to each row against each activities from 11 to 35, button is in column 'K11' while clicking on button macro will automattically add user name and time in cell Q11 and enter text as success in cell N11

I have created below button on row 11.

Sub addDetail()

Sheets("sheet1").Range("Q11").Value = _
        Environ("username") & " - " & Format(Now, "mm/dd/yyyy HH:mm:ss")
Range("N11").Value = "Success"
End Sub

do i have to create this code for 25 times to add code to each button in each row or there is any other method?

Gary's Student · Accepted Answer · 2020-06-19 13:31:29Z

2

Consider using a loop:

Sub addDetail()
    Dim roww As Long

    With Sheets("sheet1")
        For roww = 11 To 35
            .Range("Q" & roww).Value = Environ("username") & " - " & Format(Now, "mm/dd/yyyy HH:mm:ss")
            .Range("N" & roww).Value = "Success"
        Next roww
    End With
End Sub

In a similar fashion, you can create a button maker routine that uses a loop to place buttons on rows 11 through 35.

EDIT#1:

I put a small Forms button on row#11 and assigned this macro to it:

Sub Button1_Click()
    Dim s As Shape
    shapename = Application.Caller
    Set s = ActiveSheet.Shapes(shapename)
    Call addDetail(s.TopLeftCell.Row)
End Sub

The macro can be used for all the buttons.

The macro determines the row it is on and calls the addDetails() sub with the correct row information. We must modify addDetails() to accept the row:

Sub addDetail(roww As Long)
    With Sheets("sheet1")
            .Range("Q" & roww).Value = Environ("username") & " - " & Format(Now, "mm/dd/yyyy HH:mm:ss")
            .Range("N" & roww).Value = "Success"
    End With
End Sub

edited Jun 19, 2020 at 13:31

answered Jun 19, 2020 at 12:44

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

khyati dedhia Over a year ago

Hi when i click on 1st button it adds data to each row not on single row

Gary's Student Over a year ago

@khyatidedhia Do you want each button to only change the cells on the same row it resides ??

khyati dedhia Over a year ago

Hi Gary, Yes i need each button to change only same row

Gary's Student Over a year ago

@khyatidedhia See my EDIT#1

VBasic2008 · Accepted Answer · 2020-06-19 13:47:32Z

1

Application Caller feat. Buttons

Option Explicit

Sub addDetail_Click()
    On Error GoTo CleanExit
    Dim btnRow As Long
    With ActiveSheet
        btnRow = .Shapes(Application.Caller).TopLeftCell.Row
        .Cells(btnRow, "Q").Value = _
          Environ("username") & " - " & Format(Now, "mm/dd/yyyy HH:mm:ss")
        .Cells(btnRow, "N").Value = "Success"
    End With
CleanExit:
End Sub

answered Jun 19, 2020 at 13:47

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Comments

JulianG · Accepted Answer · 2020-06-19 12:32:02Z

1

Having 25 buttons next to each other is probably not the best approach. If you need this, each Shape has TopLeftCell attribute, so you can use the very same code for all 25 buttons and replace the row in the two ranges with this attribute.

If you would be happy with one button only, what about creating a set-up like this

So that you can change the row that you want to edit and just need one button?

Sub addDetail()
    dim rowA as string
    rowA = range("B2").value

    Sheets("sheet1").Range("Q" & rowA).Value = _
        Environ("username") & " - " & Format(Now, "mm/dd/yyyy HH:mm:ss")
    Range("N" & rowA).Value = "Success"
End Sub

answered Jun 19, 2020 at 12:32

JulianG

4423 silver badges8 bronze badges

1 Comment

khyati dedhia Over a year ago

Thank you, but these all activities are performed by different individual so needed different button for each activity.

Collectives™ on Stack Overflow

Multiple Button to add in Excel

3 Answers 3

4 Comments

Application Caller feat. Buttons

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Application Caller feat. Buttons

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related