Click Event for buttons inside Frames for VBA in Excel

Question

In Excel I insert an ActiveX Frame into a worksheet. Right clicking this frame allows me to select:

Frame Object>Edit

Now I am able to add a button to this frame. Great.

How do I add a _Click event to this button so that it will run a macro?

Answer 1

Basically, what you need to do is to create you own class, for instance, "XButton". Inside this 'XButton' there will be an event handler for the button object that is inside the frame.

So you can handle all of the events that are sent by 'btn' and forward it further. Then you will have to create a custom interface (empty class) IXButtonEventHandler, that will look something like this:

Option Explicit

Public Sub Click(Sender as XButton)

End Sub

So, your custom class XButton will look like this:

Private WithEvents btn as MSForms.CommandButton
Private mEventHandler as IXButtonEventHandler

Public Sub CreateObject(EventHandlerOf as MSForms.CommandButton, EventHandler as IXButtonEventHandler)
     Set btn = EventHandlerOf
     Set mEventHandler = EventHandler
End Sub

Private Sub btn_Click()
    If not mEventHandler is Nothing then mEventHandler.Click(Me)
End Sub

Let's say, your Workbook will be the event handler and will need to implement the IXButtonEventHandler interface, for instance:

Implements IXButtonEventHandler

Private Sub IXButtonEventHandler_Click(Sender as XButton)
    'your code
End Sub

On Workbook_Load or whatnot you will need to create a collection of XButtons and attach them to your frame controls:

Dim xbtn as Collection

Private Sub AttachButtons()
    Set xbtn = New Collection

    Dim i as Long

    For i = 0 to 3
       Dim xb as New XButton
       xb.CreateObject <YourFrame>.Controls("CommandButton" & Cstr(i)), Me

       xbtn.Add xb
    Next i
End Sub