从VBA中的多个按钮调用相同的SUB [英] Call same SUB from multiple buttons in VBA

问题描述

我在同一列但不同行中的电子表格上有多个按钮(活动x).这些按钮捕获活动的开始时间.如果按下按钮1，则应按当前时间填充其旁边的单元格.如果按下按钮2，则其旁边的单元格应以当前时间填充.等等.....

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.....

我已经在VBA中编写了一个SUB，如下所示:

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.

推荐答案

一个简单的WithEvents示例:

A simple WithEvents example:

在一个类中(名为clsButtons):

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

这篇关于从VBA中的多个按钮调用相同的SUB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！