Click a certain button name so it trigger the WithEvents in Class Module-CodePudding

I'm trying to learn about Class Module. So I'm playing around by making a test sub. I'm facing a problem and I've searched in the internet and in this site if there is a same question like mine.

But since I'm limited in English, so maybe I didn't put a correct sentence to search the same question - hence I can't find the question with the same problem like mine.

In the "test" worksheet, I have two ActiveX button with name btTest1 and btTest2.

In the "test" worksheet module, the code as follow :

Dim MyButtons As Collection

Private Sub Worksheet_Activate()
Set MyButtons = New Collection
For i = 1 To 2
    Set ButtonClass = New ClassTest
    If i = 1 Then Set ButtonClass.obj1 = ActiveSheet.btTest1
    If i = 2 Then Set ButtonClass.obj1 = ActiveSheet.btTest2
    MyButtons.Add ButtonClass
Next
End Sub

In the ClassTest class module:

Private WithEvents bt As MSForms.CommandButton

Property Set obj1(b As MSForms.CommandButton)
Set bt = b
End Property

Private Sub bt_Click()
MsgBox bt.Caption
End Sub

After I activate the "test" sheet, then click the "TEST 1" button it did show the msgbox "TEST 1" and when I click the "TEST 2" button it show the msgbox "TEST 2".

My question:
How to code so when "test" sheet is activated then the msgbox "TEST 1" directly show as if there is a user literally click the "TEST 1" button ?

Any kind of response would be greatly appreciated.
Thank you in advanced.

CodePudding user response：

You can simply add:

Me.btTest1.Value = True

to the end of your event code.

CodePudding user response：

Rory has fully answered your question, a little more depending on what you might be trying to achieve -

'''' worksheet module '''
Option Explicit
Private MyButtons As Collection

Private Sub Worksheet_Activate()
Dim i As Long
Dim ButtonClass As ClassTest

    If MyButtons Is Nothing Then
        Set MyButtons = New Collection
        For i = 1 To 2
            Set ButtonClass = New ClassTest
            If i = 1 Then Set ButtonClass.obj1 = Me.btTest1
            If i = 2 Then Set ButtonClass.obj1 = Me.btTest2
            MyButtons.Add ButtonClass
        Next
    End If
    
    Set ButtonClass = MyButtons(1) ' refer to the first item (a ButtonClass instance) in the collection
    
    ButtonClass.bt_Click  '

End Sub

''' ClassTest '''
Option Explicit
Private WithEvents bt As MSForms.CommandButton

Public Property Set obj1(b As MSForms.CommandButton)
Set bt = b
End Property

Public Sub bt_Click()
MsgBox bt.Caption
End Sub