1

I have multiple buttons (active x) on a spreadhseet in same column but different rows. These buttons capture the start time of an activity. If button 1 is pressed cell next to it should be populated by current time. If button 2 is pressed cell next to it should be populated by current time. and so on.....

I have written a SUB in VBA as follows:

Private Sub StartTimer_Click()
    Range("I4").Value = Now
End Sub

I do not want to repeat this code for each button action. Please let me know how it can be made dynamic.

Improve this question
2
  • 1
    Try googling "VBA control array" Commented Jul 9, 2018 at 1:40
  • If those are indeed ActiveX buttons (as opposed to Forms buttons), you would have different event handlers for each button anyway, unless you go with the approach shown by EvR. So if you don't, and you therefore have multiple handlers, the only optimization is then to have same code in all handlers, rather than slightly different code. For that you only need a way to look up the cell to change from the button object. You can code that in the button's name like EvR suggests, or you can use TopLeftCell of the button as a landmark to offset from. Commented Jul 9, 2018 at 13:13

2 Answers 2

Reset to default
3

A simple WithEvents example:

in a class (named clsButtons):

Private WithEvents Bt As MSForms.CommandButton

Property Set obj(b As MSForms.CommandButton)
    Set Bt = b
End Property

Private Sub Bt_Click()
    'uses the right of the name of the CommandButton
    Cells(1 + Right(Bt.Name, 1) * 3, 9).Value = Now
End Sub

In the sheetcode (the one with the buttons):

Dim myButtons As Collection

Private Sub Worksheet_Activate()
    Dim ctl As OLEObject
    Dim ButtonClass As clsButtons
    Set myButtons = New Collection

    For Each ctl In Sheet1.OLEObjects
        If ctl.progID = "Forms.CommandButton.1" Then
            Set ButtonClass = New clsButtons
            Set ButtonClass.obj = ctl.Object
            myButtons.Add ButtonClass
        End If
    Next ctl
    
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Another way to infer the cell to change from the button would be using the TopLeftCell of the button, given that the button is always next to the cell it should change.
1

Create a standard module and put the procedure in there.

While it is possible to share a procedure in a private module, it's best practice to put any shared procedures in a shared module.

In the VBA Editor click Insert > Module,

Paste into there, and give it a unique name. Using your example you could do something like:

Public Sub SetTimeValue()
    Range("I4").Value = Now 
End Sub

...then call this public stub from your other one, like:

Private Sub StartTimer_Click()
    SetTimeValue
End Sub

...and from any other locations where you need to call your code.

I assume that you have more than one line of code for the actual procedure you're concerned about, otherwise copying it repeatedly isn't really a concern.

More Information:

Improve this answer

2 Comments

Thanks for your reply. The problem is: I have multiple buttons StartTimer1, StartTimer2, StartTimer3.... How can I call StartTimer_Click() for each button. With each call the cell has to be different. In this example: Range("I4").Value = Now. For 2nd button it could be Range("I7").Value = Now. For 3rd button it could be Range("I10").Value = Now
If it's really only one line of code (like Range("I4").Value = Now) then you don't need any other procedures at all. Just put the one line as the code for each of the buttons. See the links I provided for more information. The last link might be especially useful to you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.