Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 16Using Embedded Controls

You've seen many ways to run macros, including using keyboard shortcuts, the Macro dialog box, and the Visual Basic Editor. This lesson shows you how to execute VBA code by clicking a button or other object that you can place onto your worksheet to make your macros easier to run.

Working with Form Controls and ActiveX Controls

A control is an object such as a Button, Label, TextBox, OptionButton, or CheckBox that you can place onto a UserForm (covered in Lessons 21, 22, and 23) or embed onto a worksheet. VBA supports these and more controls, which provide an intuitive way for you to run your macros quickly and with minimal effort.

Excel supports two generations of controls. Form controls are the original controls that came with Excel, starting with version 5. Form controls are still fully supported in all later versions of Excel, including Excel 2013. Form controls are more stable, simpler to use, and more integrated with Excel. For example, you can place a Form control onto a chart sheet, but you cannot do that with an ActiveX control.

Generally, ActiveX controls from the Control Toolbox are more flexible with their extensive properties and events. You can customize their appearance, behavior, fonts, and other characteristics. You can also control how different events are responded to when an ActiveX control is associated with those events.

Form controls have macros that are assigned to them. ActiveX controls run procedures that are based ...