What are the Active X Controls?
Active X controls are like Form Controls on Steroids in that they have a much wider range of properties than Form Controls. They also have much better ties to VBA in terms of programmability and have a number of events that can be accessed programmatically. The main limitation of Active X controls are that they use a Microsoft Active X component. This means that if you are sharing your workbook with an Apple Mac user using Excel for Mac these functions wont be available as Active X isn’t available on that Platform. Workbooks with Form Controls will happily work on a an Apple Mac.
ActiveX controls can be used on worksheet forms, with or without the use of VBA code, and on VBA UserForms. In general, use ActiveX controls when you need more flexible design requirements than those provided by Form controls. ActiveX controls have extensive properties that you can use to customize their appearance, behavior, fonts, and other characteristics.
You can also control different events that occur when an ActiveX control is interacted with. For example, you can perform different actions, depending on which choice a user selects from a list box control, or you can query a database to refill a combo box with items when a user clicks a button. You can also write macros that respond to events associated with ActiveX controls. When a user of the form interacts with the control, your VBA code then runs to process any events that occur for that control. Your computer also contains many ActiveX controls that were installed by Excel and other programs, such as Calendar Control 12.0 and Windows Media Player.
Not all ActiveX controls can be used directly on worksheets; some can be used only on Visual Basic for Applications (VBA) UserForms. If you try to add any one of these particular ActiveX controls to a worksheet, Excel displays the message “Cannot insert object.” However, ActiveX controls cannot be added to chart sheets from the user interface or to XLM macro sheets. You also cannot assign a macro to run directly from an ActiveX control the same way you can from a Form control.
Worksheet with Form and ActiveX controls
A worksheet is a type of form that enables you to enter and view data on the grid, and there are several control-like features already built-in to Excel worksheets, such as comments and data validation. Cells resemble text boxes in that you can enter and format them in a variety of ways. Cells are often used as labels, and by adjusting cell height and width and merging cells, you can make a worksheet behave like a simple data entry form. Other control-like features, such as cell comments, hyperlinks, background images, data validation, conditional formatting, embedded charts, and AutoFilter can make a worksheet behave like an advanced form.
For added flexibility, you can add controls and other drawing objects to the drawing canvas of a worksheet, and combine and coordinate them with worksheet cells. For example, you can use a list box control to make it easier for a user to select from a list of items. Or, you can use a spin button control to make it easier for a user to enter a number.
Because controls and objects are stored on the drawing canvas, you can display or view controls and objects alongside associated text that is independent of row and column boundaries without changing the layout of a grid or table of data on your worksheet. Most of the time, many of these controls can also be linked to cells on the worksheet and do not require VBA code to make them work. You can set properties that determine whether a control floats freely or moves and resizes together with a cell. For example, you might have a check box that you want to move together with its underlying cell when the range is sorted. However, if you have a list box that you want to keep in a specific location at all times, you probably do not want it to move together with its underlying cell.
Excel has two types of controls: Form controls and ActiveX Controls. In addition to these sets of controls, you can also add objects from the Drawing tools, such as a AutoShapes, WordArt, SmartArt graphic, or text boxes. The following sections describe these controls and drawing objects, and also explain how to work with these controls and objects in more detail.
Other Controls Available in Excel
A number of other Excel objects can be used to add interactivity to your worksheets.
- These include:
- Text Boxes
- Word Art
All these can have macro’s linked to them which effectively act the same as a Button Form Control without the moving button effect.
Drawing Tool objects
You may also want to include SmartArt graphics, Shapes, WordArt, and text boxes on your form. You can resize, rotate, flip, color, and combine these objects to create even more complex shapes. When you type text directly in a Shape or text box object, the text becomes part of that object — if you rotate or flip the object, the text rotates or flips with it. Unlike ActiveX controls, you can assign different attributes, such as font size and font style, to individual words and characters in the object. You can also assign macros and add hyperlinks to these objects. You can even link text in a Shape or text box object to a worksheet cell and dynamically display updated values in those objects.
Working with controls and objects on the worksheet form
After adding forms and ActiveX to a worksheet form, you usually want to fine-tune and rearrange the controls in a variety of ways to create a well-designed, user friendly form. Common tasks include the following:
- Controlling the display of gridlines while you work with the controls, and deciding whether to display the gridlines to the user on the final worksheet form.
- Selecting and deselecting controls so that you can specify properties or make additional adjustments.
- Editing text in a control, such as the caption or label.
- Grouping, copying, moving, and aligning controls to organize the layout of the worksheet form.
- Resizing and formatting controls to obtain the appearance that you want.
- Positioning or sizing a control with a cell.
- Protecting controls and linked cells according to your specific data protection needs.
- Enabling or disabling the printing of controls when the worksheet form is printed.
- Deleting unused controls.
- You can design a worksheet form with or without cell gridlines in the background. For example, you might want to turn off cell gridlines and then format all the cells with the same color or pattern, or even use a picture as a sheet background. To hide or show the gridlines, on the View tab, in the Show/Hide group, clear or select the Gridlines check box.
Determining the type of control that is on your worksheet
Because there are three different types of controls and objects that you can modify uniquely, you might not know for sure which type of control it is just by looking at it. To determine the type of control (Form or ActiveX), select and right-click the control, and then display the shortcut menu:
If the shortcut menu contains the command Properties, the control is an ActiveX control, and you are in design mode. If the shortcut menu contains the command Assign Macro, the control is a Form control.
To display the correct shortcut menu for the group box Form control, make sure that you select the perimeter instead of the interior of the group box.
If the shortcut menu contains the command Edit Text, the object is a Drawing object.
For maximum flexibility, you can create UserForms, which are custom dialog boxes, that usually include one or more ActiveX controls. You make UserForms available from VBA code that you create in the Visual Basic Editor. The high-level steps for creating a UserForm are as follows:
Insert a UserForm into your workbook’s VBAProject. You access a workbook’s VBAProject by first displaying the Visual Basic Editor (press ALT+F11) and then, on the Insert menu, clicking UserForm.
- Write a procedure to display the UserForm.
- Add ActiveX controls.
- Modify properties for the ActiveX controls.
- Write event-handler procedures for the ActiveX controls.
- By using UserForms, you can also utilize advanced form functionality
For example, you can programmatically add a separate option button for each letter of the alphabet or you can add a check box for each item in a large list of dates and numbers.
Before creating a UserForm, consider using built-in dialog boxes available from Excel that might fit your needs. These built-in dialog boxes include the VBA InputBox and MsgBox functions, the Excel InputBox method, GetOpenFilename method, GetSaveAsFilename method, and the Dialogs object of the Application object, which contains all the built-in Excel dialog boxes.