CODEWITHSUNDEEP
vbaexcel
Stef
Stef

Reputation: 3

Excel-VBA Capture all worksheet CommandButton click events to run universal code

I would like to have a piece of code that runs when any worksheet commandbutton is pressed. I have seen similar things for userforms but can't find something that works across (and limited to) worksheet buttons.

I have got as far as creating a class module called clsCmdButton with the following code:

Private WithEvents cmdButton As MSForms.CommandButton

Private Sub cmdButton_Click()
'code i want to run
End Sub

I then think I need to loop through all worksheets, find the commandbuttons on it and intialise each as a new insance of the class. This could be done in the Workbook_Open event, however I am not sure how to code it. Could anyone help?

Thanks!

Upvotes: 0

Views: 2457

Answers (1)

Rory
Rory

Reputation: 34045

I'd amend your class like this:

Private WithEvents m_ctlButton As MSForms.CommandButton

Private Sub Class_Terminate()

    Set m_ctlButton = Nothing

End Sub


Private Sub m_ctlButton_Click()
'code i want to run
End Sub

Public Property Set Button(ctlButton As MSForms.CommandButton)

    Set m_ctlButton = ctlButton

End Property

Let's assume this is Class1 as I'm lazy.

Now in a new module add this:

Option Explicit
Dim colButtons As Collection
Sub hookButtons(ws As Object)
    Dim oBtn As Class1
    Dim obj As OLEObject
    Set colButtons = New Collection
    For Each obj In ws.OLEObjects
        If TypeOf obj.Object Is MSForms.CommandButton Then
            Set oBtn = New Class1
            Set oBtn.Button = obj.Object
            colButtons.Add oBtn
        End If
    Next
End Sub

and finally, in the ThisWorkbook module, add this:

Private Sub Workbook_Open()
    hookButtons ActiveSheet
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    hookButtons Sh
End Sub

Since you can't click a button without its sheet being active, it seems easier to only hook the active sheet's buttons at any given time. Using the sheetActivate event should also mean that it gets reset more often in case of unhandled errors in other code in the workbook.

Upvotes: 2

Related Questions