Take control of your buttons
If you are working on a VBA model in Excel, and you want to make it look even smoother, you can link your macros to ActiveX command buttons.
ActiveX Controls allow you to create not only clickable buttons, but also to create fields for user input. For example, these fields can be personalized for the kind of input required or the number of characters an input field can contain. Examples of ActiveX Controls are buttons, check boxes, text boxes, and list boxes.
ActiveX Controls can be found in the Controls group on the Developer Tab, under the Insert command. From the Insert command, you can choose either a Form control or an ActiveX Control as shown in the image below.
The ActiveX command button is the most widely used ActiveX Control. And there are a few advantages to using ActiveX Controls rather than basic Forms Control.
- ActiveX Controls allow for flexible design requirements
- ActiveX Controls have extensive properties settings that you can use to customize their appearance, behaviour, fonts, and other characteristics.
- ActiveX Controls can be used on UserForms and not only in worksheets.
- ActiveX Controls can be used as objects in codes
Linking ActiveX Controls to macros
As soon as you click View Code in the Developer tab, or right-click the ActiveX command button in Design Mode and select View Code, Excel creates a macro that is linked to the button.
With ActiveX command buttons, the macro you want to link to the button is written after the button is created. Whereas when using normal shapes, the macro is written first and then it is simply selected in the dialog box when assigning the macro.
Triggering the macro
By default, ActiveX buttons are triggered when you left-click them. This can easily be changed through the Code window in the VB Editor. The two most commonly used triggers are the single-click and the double-click.
To change the trigger first you need to add the macro connected to a single-click, and then to add the macro for the double-click.
Note that this doesn’t work in combination with the single-click. So, you need to remove the single-click for the double-click to work.
Alter ActiveX Control properties
Formatting the ActiveX button requires you to have access to Design Mode as only then can you right-click the button and show its properties. To enter Design Mode, go to the Developer tab, and then Design Mode.
An alternative way to access the properties of an ActiveX button is to select the button, go to the Developer tab, and open Properties (Excel still needs to be in Design mode).
Referring to ActiveX Controls
It's possible to refer to an ActiveX control in your macro, just like you can refer to worksheets, workbooks, and other Excel objects. But when you refer to an ActiveX control, you must ensure that you refer to the name of the control and not the text displayed on the control.
Sign-up at 5miles today for a free two-week trial and see how much nicer your VBA model can look after you complete the 70+ VBA Challenges.
