0

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!

Stef
  • 3
  • 2
  • If you use a Form button, you can just assign the same macro to all of them. They are also a lot more stable than ActiveX buttons. – Rory Oct 11 '17 at 11:57
  • Hi Rory, thanks for getting back to me. This is an old spreadsheet with lots of buttons and code. I know to avoid ActiveX controls now but when this was developed they were all created as ActiveX. Also there is a lot of interaction between buttons being enabled and disabled. The code I want to run is actually a piece of code that deals with the button resizing issue. Rather than put a call to the procedure at the beginning of every button click event I want a universal event that will catch a button click, run that extra code and then continue with the button's own click event as usual. – Stef Oct 11 '17 at 12:24

1 Answers1

2

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.

Rory
  • 32,730
  • 5
  • 32
  • 35
  • That's brilliant. Works great, except the m_ctlButton_Click() event isn't firing. The collection is created and the buttons are in it but when they are clicked they just run their own code and not the class event. Any ideas why? – Stef Oct 11 '17 at 12:51
  • I'm afraid not. I tested the code and it works here, so I'd have to guess that maybe something the buttons are doing is interfering. If you can post a link to a workbook that shows it not working, I'll have a look when I have a minute. – Rory Oct 11 '17 at 13:37
  • Hi Rory, I've found the issue. The class event fires after the button's own event. As the button code activates another sheet, the worksheet activate event kills the class button that's been created and therefore its event. I guess there is no way of getting the class event to fire before the button event is there? – Stef Oct 12 '17 at 08:36
  • No, but you could amend the code to simply populate the collection on workbook open for all the sheets, rather than just doing the active one on each sheet's activation. – Rory Oct 12 '17 at 08:50
  • ok, i'll make that change. Also, is there a way of finding out which sheet a button is on? When the class event fires it needs to know the sheet the button that called it is on, but as the button code has changed sheet it isn't the activesheet anymore. – Stef Oct 12 '17 at 09:02
  • You could add that as another property of the class or, if it's only needed by the class, use a private variable. – Rory Oct 12 '17 at 09:34