CODEWITHSUNDEEP
excelvbaexcel-2010
karma
karma

Reputation: 2009

Click a certain button name so it triggers the WithEvents in Class Module

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

In the "test" worksheet module:

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 shows the msgbox "TEST 1" and when I click the "TEST 2" button it shows the msgbox "TEST 2".

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

Upvotes: 0

Views: 61

Answers (2)

Peter T
Peter T

Reputation: 316

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

Upvotes: 1

Rory
Rory

Reputation: 34085

You can simply add:

Me.btTest1.Value = True

to the end of your event code.

Upvotes: 1

Related Questions