Using controls on worksheets

Two groups of controls; the differences

By now it is apparent that there are two distinct series of controls: Those from the forms group and those from the Control toolbox (named ActiveX controls in Excel 2007 and 2010).

Pro's and con's

The table below lists some advantages and disadvantages of both control sets:

Control type	Form controls	ActiveX controls
Advantages	Simple to use Can be used on chart sheets Assigning control to a macro is simple Little known problems	Lots of options Lots of events (VBA) Lots of formatting options Lists return the selected value rather than the index number
Disadvantages	Lists return the index number rather than the selected value	Cumbersome to use one macro for multiple controls Sometimes cause of trouble with file corruptions

Which should you use

By now you'll be wondering which set you should use. Generally speaking, I recommend using the controls from the forms toolbar. If you have specific needs regarding formatting which cannot be achieved using the forms controls (or if you want to program events in VBA), then you'll have to switch to the ActiveX controls (control toolbox controls).

Inserting controls

Inserting a control on your sheet is very simple: Just click the control you need (See fig. 2 and 4 on the previous page) and drag a rectangle on the sheet at the position where you want the control to appear. You can also just click on the sheet and have Excel decide what dimensions to use for the control.

If you hold down the alt key when you click on the worksheet, then the control will be aligned to the cell grid. You can also hold the alt key when you are dragging the control or resizing the control to have it snap to the grid. This is a quick way to ensure your controls are nicely aligned and of equal size.

Double click a control on the toolbar or on the Insert controls dropdown if you want to draw multiple copies of that control. Click that control again (or any other control) to get out of that mode.

Comments

All comments about this page:

Comment by: burnout (12/30/2009 12:56:35 PM)

spelling in form control advantages:
"Assigning contorl to a macro is simple"

Comment by: Jan Karel Pieterse (12/31/2009 4:03:46 AM)

Hi Burnout,

Good catch, fixed. Thanks!

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.