As part of an elegant design, controls add professional look and feel to your Excel project. They make selections intuitive, giving users a friendly interface. Think of an online survey or quiz, and the usefulness of controls is evident. Purpose comes first, and the simplest solution is best. Like macros, controls are advanced tools in your kit to be used when there is no simpler answer.
Consider for example the case of data entry. A typical data entry form has many controls, so it’s natural to think of adding controls for entering data in a sheet. But where do the data go once they’re entered? Controls have a “linked cell” property to catch the entry, but then what? You would have to process that cell to commit the record to a table. The simple solution is to enter data directly in the table or via the Form tool, as described in the Data Entry section. If you really need a business form and you’re struggling to make it in Excel, stop and think about purchasing the right tool for the purpose.
The most common control I see people use is a button to run a macro. If you’ve written a macro for the user to run, you wouldn’t expect them to find it the long way. A button control is a natural way to say “run something”, but there’s nothing special about a button; any shape, picture or icon can also do the job, which gives you a lot more visual options. Simply right-click the object and select Assign Macro. Make it clear that the object is interactive and what it does, either by appearance or by label.
I like to use controls to enhance the user interface even without VBA; a checkbox to switch a condition on and off; a spin button or scroll bar to slide from low to high value. These are intuitive invitations to interact safely with the sheet.
Another type of control is used to select data from a list, such as a combo box. Such controls add the value of exposing not only what was selected, but from what options. For this purpose controls can be used with or without VBA coding. Their input and output are linked to sheet ranges. Competing options here include table filters, pivot tables, Data Validation and slicers.
Control Families
Controls like VBA are on the Developer tab, which if you have not yet enabled you must select in options; Alt-F,T,C, then check Developer on the right. Once you decide to use controls, the first question is, Form or ActiveX? They have mostly the same controls, so what’s the difference? Initially, not to worry, either one will do. Form controls are easier to set up and learn, and are adequate for most uses. As you encounter limitations, you can start to learn and incorporate ActiveX into your toolkit.
Once you understand Form vs. ActiveX, learn their navigational differences. To set Form control properties, right-click and select Format Control. To set ActiveX control properties, first set Design Mode on the Developer tab, Alt-L,D,M, then select the control, then Properties, Alt-L,L. To program a Form control, write the macro, then right-click the control and select Assign Macro. To program an ActiveX control, in Design Mode double-click the control to open the default event in VBA, or, in VBA double-click the sheet, in the first drop-down list select the control, and in the second drop-down list select the event.
Control Types
Here are some points to consider when selecting controls. I’ve included some non-control options I call “faux controls” that look and feel like controls, for comparison. In the descriptions following, I’ll refer to ActiveX properties in the format [Property], and Form control properties, if they exist, as {Property}.
Combo Box combines features of List Box, presenting a list of pre-defined values, and Text Box, allowing new entries. The [Style] property gives you the option to either limit entries to the list (List style) or allow non-list entries (Combo style). The Form Combo Box does not have the new entry option and thus is not a true combo box. A Combo Box does not reveal its value list until the drop-down is clicked.
List Box can be sized to show its entire list, eliminating scroll bars. It does not have an option to accept non-list entries, but it does have the option to select more than one entry from the list with the [MultiSelect] {Selection type} property. Combining [MultiSelect] with [ListStyle], an ActiveX List Box can be made to look and behave like an Option group or a Check Box group.
This is a convenient alternative to set up a single control instead of several. Note, a multi-select List Box negates the simple output cell link, and VBA is required to retrieve the selections. Stick with single-select to keep it simple, or a checkbox group to retrieve each selection without VBA.
A Check Box group is like a multi-select List Box, but since each control has its own linked cell, there is no need for VBA. Check Boxes can be used individually as a toggle (see below), or in a group for independent options. Here the group is a convenience only, each option is retrieved and evaluated on its own. On a form this might be labelled “check all that apply”.
An Option Button group is a collection of dependent options. An individual Option Button has no purpose by itself, it can only exist in a group with at least one other Option Button, like a single-select List Box. Each button must be identified with a group, because only one option within the group can be selected. With Form Option Buttons, the {Cell link} property defines the group; all Option Buttons linked to the same cell are logically grouped, and the cell displays the index number of the selected button. With ActiveX, the [GroupName] property defines the group, and each button has its own LinkedCell that contains its Boolean state.
Spin Button and Scroll Bar are both ways to select a number between the defined [Min] {Minimum value} and [Max] {Maximum value} by a defined [SmallChange] {Incremental change} increment when the arrow buttons are clicked or an arrow key is hit. Scroll Bar adds a [LargeChange] {Page change} increment when the bar between buttons is clicked or a page button is hit. As an example guideline, I think of Spin Button for a range of 1 to 10 and Scroll Bar for 1 to 100. These are useful for any numeric field with reasonable range, or to navigate a set of pre-defined numbered scenarios.
Toggles are Boolean controls, Toggle Button (ActiveX only) and Check Box. These are great for output controls with clear yes/no, on/off options, like “show chart”, or “include holidays”. They can be used for any two-answer question, but are best reserved for true Booleans. For example, “domestic or global” appears to only have two values, but might later evolve into a list. ActiveX toggle controls also have a [TripleState] property. If [TripleState] is true it adds N/A to the standard true and false cycle with a grayed-out appearance. The Form Check Box displays gray if the linked cell contains N/A, but it cannot set the N/A state.
The Startup file Controls sheet gives examples of these controls and features. Various controls are used in MRP Demo, and the Startup Calendar and Startup Colors.
The first faux-control, Data Validation is like a Combo Box that can be set up once for a range, and the drop-down list is enabled when each cell is selected. The choice to allow new entries or not is made in the Error Alert tab; if Style = Stop, no new entries are allowed, like Combo Box “List” style, otherwise new entries are allowed, like Combo Box “Combo” style. This method is used in several projects for configuration settings. An input example is in Team Calendar.
The second faux-control, Slicer is similar to an “extended” multi-select List Box; multiple options can be selected while holding Ctrl. A Slicer is an output selector linked to the data itself, so as new values are added to the sliced field, new buttons are added to the slicer. Examples of Slicers are noted in the Slicers section.
There is another faux-control option using symbols that look like controls. This requires VBA to control interaction and set the appearance. This method is used in Project Tracker.
Sheet Design
The choices of Form or ActiveX and which control to use in each case are numerous. Adding visual criteria helps narrow the choice. How do you want the sheet to look? How many controls do you need, and how much space do you have? It is important to have a deliberate design strategy, including logical and visual flow, labelling, spacing and alignment. Think about your car dashboard to get the idea.
A Combo Box consumes little space. It can be but does not need to be sized and aligned to a cell, although font size might need adjustment to avoid cropping. The drop-down exposes a good chunk of the list. The default is eight items and it can be increased to avoid a scroll bar. A Data Validation drop-down list is of course by nature a part of the cell, so how good it looks depends on the size and position of the cell. It also shows eight items, which cannot be increased.
A List Box looks better as an expanded list, requiring more space. Sized well it will have no scroll bars. The color palette is limited, but enough to blend nicely into the sheet. As noted above this is a nice alternative to an Option Button group.
Get familiar with the Align tools, Alt-P,A,A. Start with Snap to Grid, then nudge to balance. If controls are poorly balanced with other elements of the sheet, they will detract from the appearance rather than enhance it. If not aligned to the grid, then at least align and space them nicely as a group. For example, for a vertical option group, select all the option buttons and Align Left, and Distribute Vertically. A Group Box control is not technically required, but is a good way to set groups of controls apart.
Control size is visible for all except check boxes and option buttons, which have an invisible border. Only when you select the control – right-click for Form, Design Mode-click for ActiveX – do you see the border. Make grouped controls the same size with Shape Format, Alt-J,D, H and W, and such that once arranged, their borders do not overlap. A click anywhere within the border sets the control, and if they overlap this can cause clumsy behavior and unexpected results.
Meanwhile behind the scenes, each control needs to be configured. For Combo Box and List Box, input range is best stored and named, hidden or on a separate config sheet. Output cells can also be stored and named on the same config sheet. Use naming convention to logically connect the control with its input list and output cell. For example, if an input list of brands is named BrandList, name the control like BrandBox, and the output cell like BrandSelected.
Some pick lists never change. Others may seldom change, but are best designed to accommodate change, such as a product line or customer group. In these cases it’s worthwhile to create a table and a name to make it easy to update. The format for the name is Name=Table[Field]. You don’t need to use a table for pick lists, you can simply name the range, but if you ever add items to the list, be sure to update the range name. If you insert a cell within the list, the named range will be naturally extended. If you add an item at the bottom of the list, you must manually update the list name to include the new item. The table does this bottom-extension automatically.
The linked output cell may or may not require processing to achieve the desired effect. Form selection boxes output the index of the selection within the list, while the ActiveX counterparts output the selected item itself. To retrieve the item from a Form selection, use =INDEX(InputList,OutputCell). On the other hand, if you need the index from an ActiveX control selection, use =MATCH(OutputCell,InputList,0). Unless used directly, OutputCell can be hidden.
Navigation
All true controls, as well as Data Validation dropdowns and Slicers, are objects on the sheet, like shapes or pictures. They can be viewed, selected, renamed, sorted and hidden in the Selection Pane, Alt+F10. They are mass-hidden and un-hidden by Ctrl+6, but this does not change the individual object’s hidden setting. If you can’t find a control or drop-down, try Ctrl+6 and Alt+F10 to see if it’s hidden.
User interaction with Form controls is by mouse. There is no need to select the control except to configure it. As developer, you can use Ctrl+Alt+5 to select the first object on the sheet, which is at the bottom of the Selection Pane, and Tab to move up the list from there. Objects can be sorted to change Tab order.
The user can interact with ActiveX controls by key once they have the focus, that is, when they appear selected – but without the resizing handles. For example, if you click an ActiveX Check Box, it puts a dotted outline around the name. You can then use the Spacebar to set or unset it. Esc releases the focus back to the sheet. ActiveX controls can only be actually selected in Design Mode, and will interrupt the Tab cycle if Design Mode is turned off.
Users can navigate Data Validation dropdowns by key, first to select the cell, then Alt+↑ or Alt+↓ to open the list, ↑ or ↓ to scroll it, and Enter to select.
Slicers can be user-navigated and selected by Ctrl+Alt+5 followed by Tab, arrows, Shift+arrows, Space, Ctrl+Space, Enter, Ctrl+Enter. With a little practice this can be very productive. Multiple Slicer banks should be sorted in the Selection Pane to support object Tab navigation.
Allow users to use but not select controls. To disable selection of Form controls, or ActiveX controls in Design Mode, protect the sheet with Edit objects unchecked. Linked cells must be unlocked when the sheet is protected, if on the same sheet.
If the sheet has Slicers, Edit objects must be checked, as well as Use AutoFilter if table slicers, and Use PivotTable if pivot slicers. Since you can’t disable slicer selection with sheet protection, a good alternative is to set Size and Properties → Position and Layout → Disable resizing and moving. This removes resizing handles and prevents inadvertent nudging.
To create a form with Tab navigation between controls, use a custom VBA form. This is seldom justified and is out of the scope of this book.
Comments