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?

In a form or on a worksheet? Have you tried just double-clicking the button? — Tim Williams, Mar 19 '13 at 21:41
On a worksheet, in a Frame. In design mode, get into editing the Frame (Frame Object>Edit). Double clicking isn't the same as an ActiveX button directly into the sheet. — Robino, Mar 19 '13 at 21:49
I see what you mean.. you could try trapping the `AddControl` event on the form (click 'view code', then in the code editor select the form in the LH dropdown, followed by the AddControl event in the RH dropdown). From there, you can maybe add an event handler? — Geoff, Mar 19 '13 at 21:55
Is this code only run when another control is added to the Frame? Incidentally, how would I add the event handler? — Robino, Mar 19 '13 at 22:03
One approach to capturing the button click event here: http://www.xtremevbtalk.com/archive/index.php/t-236100.html This worked for me in Excel 2010 — Tim Williams, Mar 19 '13 at 22:53
Wouldn't it be nice if one could just double click on it like in your first comment!?!? *sigh* — Robino, Mar 19 '13 at 23:30
That's strange, I can't add a button to 'edited' ActiveX frame... Any idea? — Kazimierz Jawor, Mar 20 '13 at 06:34
@KazJaw Right click frame>Frame Object>Edit. Then right click frame>Toolbox... Now you can use the controls in the Frame's toolbox. — Robino, Sep 30 '14 at 13:28

Zhenya · Accepted Answer · 2019-03-25T15:36:09.390

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

+1 This is a great answer to a non-trivial problem. Welcome to SO! — Robino, Mar 20 '13 at 19:14
I would like to add that if you work with ActiveX controls and call them by `currCtrl = sheet)"mysheet").OLEObjects.Item(i)` you have to add them to the [array](https://stackoverflow.com/questions/3014421/how-to-add-events-to-controls-created-at-runtime-in-excel-with-vba) or [collection](https://stackoverflow.com/questions/14994235/handling-events-for-oleobject-commandbuttons-created-at-runtime) by `... = currCtrl.Object` as described [here](https://stackoverflow.com/questions/2368252/excel-vba-how-to-cast-a-generic-control-object-into-a-combobox-object). — Stefan, Jul 25 '18 at 08:19

Click Event for buttons inside Frames for VBA in Excel

1 Answers1