Using controls on worksheets
Detailed description of the controls (4)
The most important reason to use a scrollbar is to be able to change a value very quickly. The user can drag the scroll button, click next to the scroll button to change "page" or click the arrows to increase or decrease the value stepwise.
Fig 12: Scrollbar with linked cell.
A vertically placed scroll bar works opposite compared to a spinner control. Clicking the up arrow on a spinner increases the value; clicking the up arrow on a scroll bar decreases the value. In my opinion, the former is more intuitive. If I need this vertical setup, I prefer to use a spinner over a scroll bar.
A scroll bar enables you to change the value in two ways. The "Incremental change" is performed by clicking the arrow buttons, the "Page change" is performed by clicking next to the scroll button. See fig. 13:
Fig 13, Two levels of step sizes.
These properties have a different name for the scroll bar of the Control Toolbox toolbar (ActiveX) "SmallChange" and "LargeChange" respectively, see figure 14:
Fig 14, Setting properties of the ActiveX controls scroll bar.
Scroll bars can only work with integers. The range you can use differs between the two families. The forms control ranges from 0 to 30,000. The ActiveX control can go as high as 666,666.
If you need steps less than 1, then use a calculation. For example if you need a step size of 0.5, divide the linked cell's value by 2.
If you want to be able to quickly change a cell value stepwise, the spinner is the place to be.
Fig 15: Spinner controls.
Setting up spinner controls works identical to the scroll bar control, using the same properties. Of course the spinner does not have a Page change (LargeChange) property.
There is only one text box control, member of the ActiveX family of controls, on the Control Toolbox toolbar in Excel 2003 and before. I find this control to have little use, because you can simply use a cell and enter text into the cell directly.
The last control I discuss here is the toggle button. This is another example of a control that is only available through the ActiveX family of controls. In my opinion, this control is ambiguous. It could be used to either indicate an action, or a state. You could use a control like this to change the page setup of a sheet from portrait to landscape and vice versa. Disadvantage of this control is that the state and action paradigm are conflicting, especially if the two states have a different name (like in the example).
Suppose you want to enable toggling between portrait en landscape. You might be tempted to use a toggle button, which has some VBA attached to it to set the option and which updates the caption of the control. What does the caption indicate, the current status, or the status AFTER clicking the toggle button?
Fig 16: Ambiguity when using a toggle button: which one indicates we're in Portrait mode?
Due to this ambiguity a toggle button is only useful to indicate an on or off state for a property which has the same name in both states. For this goal, a checkbox is to be preferred. If there are two mutually exclusive choices, consider using two option buttons.
Interaction between controls and worksheet cells
Each control can be tied to a cell and thus (by using that cell in your formulas) affect your spreadsheet model directly and drive interactivity between your model and the user. For the forms controls, you access these options by right-clicking the control in question and selecting "Format control". Each control offers its own set of options, which are located on the "Control" tab. (see figures 11 en 13). Similarly, you can change the properties of the Control toolbox (ActiveX) controls using their properties window.