Hooking an ActiveX Command Button in Class Module
This article describes how to assign an ActiveX CommandButton on a worksheet to a member of a custom class in a VBA project. When creating an application in Excel it is likely that somewhere you will want one or more clickable “buttons” in a sheet. A quick and easy way of doing this is to add a “Shape” to the sheet, right-clicking it and selecting “Assign Macro”. You can then write code that will run when the shape is clicked by a user. This is a neat solution because the appearance of a shape is highly customisable, allowing you to create an attractive user interface. There is one drawback with this option: a shape does not have built-in events, which means it is of limited use as a member of a class module if you need to declare it “WithEvents”.
Consider this scenarios: you want to develop an Excel Add-In that needs to display a sheet with clickable buttons to the user. It would be easy to use a userform instead of a worksheet as the display canvas, but sometimes it is preferable to use the worksheet instead. You have two options: create the sheet at run-time, or create the sheet in the Add-In workbook and create a copy of it at run-time. The question is, how do you make the buttons on the sheet clickable with code behind the click event?
Let’s say you create the sheet as part of the Add-In workbook (the same arguments will apply to the other option). To expose the sheet to the user you will have to copy it to a new workbook (sheets in an Excel Add-In are always hidden). If you choose to add shapes to the sheet to be used as button and you assign a macro to a button, the macro code will reside in a code module in the VBA Project of the Excel Add-In, or it can reside in the sheet’s code module. If it resides in a code module, then the macro will not be available after copying the sheet to a new workbook and clicking it will result in an error message. If it is placed in the sheet’s code module then the macro will be copied along with the sheet to the new workbook and that way the button will at least still be clickable with executable code behind it. But this solution is very limited in that the code behind the button cannot refer to any of the variables or objects in your Add-In, or any other parts of your Add-In.
The alternative is to use Command Buttons. I prefer “hooking” these command buttons by adding them as members to a class module and keeping everything in memory by instantiating the class as a global object variable. For example, create a class module named CSheet and in a standard code module create a global variable to hold the class object:
Public gC_Sheet As CSheet
In the class module, add a member to hold the sheet object and buttons and their properties as members of type CommandButton:
Private WithEvents Sheet_ As WorkSheet Private WithEvents Button_ As CommandButton
Public Property Set Sheet(ws As WorkSheet) Set Sheet_ = ws End Property
Public Property Get Sheet() As WorkSheet Set Sheet = Sheet_ End Property
Public Property Set Button(b As CommandButton) Set Button_ = b End Property
Public Property Get Button() As CommandButton Set Button = Button_ End Property
Because you declared the button WithEvents, and because a CommandButton ActiveX control has a built-in “Click” event, you can now add some code that will run when the button is clicked:
Private Sub Button__Click() … ‘Your code here … End Sub
Finally, you need to “hook” the CommandButton on the sheet to the class member:
Private Sub Class_Initialize()
Set Me.Sheet = Sheet1 ‘Whatever the codename of the sheet in your Add-In workbook is Set Me.Button = Me.Sheet.OLEObjects(“Button1″).Object
End Sub
The last line of code is key: there is no “CommandButtons” collection in an Excel Worksheet, so we cannot refer to a CommandButton by doing something like
Set Me.Button = Me.Sheet.CommandButtons(“Button1″)
And because we had to declare our button member as a CommandButton it will lead to a type mismatch if we try to simply hook it to the OLEObject:
Set Me.Button = Me.Sheet.OLEObjects(“Button1″)
So the key is to use the “Object” member of the OLEObject: it will match the type of our button member. You can test this in the immediate window:
?Typename(Sheet1.OLEObjects(“Button1″)) OLEObject
?Typename(Sheet1.OLEObjects(“Button1″).Object) CommandButton
Tags:Classes CommandButton VBA
