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 > 2 groups
Deze pagina in het Nederlands

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 up).

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!

 


Comment by: John Tang (1/23/2011 2:17:42 PM)

I tried out Tip No.2 to draw multiple copies of a selected form control but found out that it does not work. I use Excel 2007 version. Appreciate your clarification.

 


Comment by: Jan Karel Pieterse (1/23/2011 10:48:35 PM)

Hi John,

You're right, as of Excel 2007, you can no longer double-click a control in the toolbox so you can draw more of them without having to go to the ribbon again. Pity! I guess the fastest way around this new limitation is by copying and pasting them.

 


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].