Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Controls > Descriptions (4)
Deze pagina in het Nederlands

Using controls on worksheets

Detailed description of the controls (4)

ScrollBar

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.

Schuifbalk met gekoppelde cel

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:

OP 2 niveau's instelbare stapgrootte

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:

SmallChange en LargeChange eigenschappen

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.

Spinner

If you want to be able to quickly change a cell value stepwise, the spinner is the place to be.

Een kringveld

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.

TextBox

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.

ToggleButton

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?

Wisselknop; Onduidelijk wat de knop aangeeft

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.


 


Comments

All comments about this page:


Comment by: Paul Simpson (3/14/2011 10:00:25 AM)

So change the Caption of your ToggleButton based on the state. Good-bye ambiguity.


If tglButton Then
     tglButton.Caption = I'm ON now"
Else
     tglButton.Caption = "OFF again"
End If

 


Comment by: Jan Karel Pieterse (3/14/2011 10:36:28 AM)

Hi Paul,

I can't say I agree.
Though this means the toggle button's caption reflects its state, it now no longer tells the user what happens if you push it. Two option buttons are a better way to show both state and how to change that state if you ask me.

 


Comment by: Tucker (7/20/2013 11:43:18 PM)

I have the scroll bar in place, I just can't figure out how to connect it to the data/script (i.e. I scroll the scrollbar over, and nothing happens). How do you link the data to the scroll bar so that the scroll bar actually moves the script? Thank you.

 


Comment by: Jan Karel Pieterse (8/8/2013 9:10:17 PM)

Hi Tucker,

The basis is to open a new instance of the class and assign the scrollbar to it's control object that is declared "WithEvents" inside that class. As the example workbook shows you.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].