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

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

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 (1)
Deze pagina in het Nederlands

Using controls on worksheets

Detailed description of the controls (1)

Label

The label control is the simplest control available, all it can be used for is to display descriptive text. Use this control if you want to add some explanatory text to another control.

TIP: You can make sure the label text is derived from a worksheet cell. To do so, select the label and then click in the formula bar and enter a reference to the cell. See fig. 5.

Label tekst hangt af van celwaarde

Fig 5: The text on a label drawn from a worksheet cell

Frame

You can use a frame to visually group controls with a shared purpose. Apart from that, the frame control has a specific function for option button controls (see the appropriate section about them). You must start with drawing the frame control before adding the controls you want placed "inside" the frame. To make this a painless process, start out by drawing a relatively large frame (you can make the frame smaller later on). After that, draw the controls inside the frame:

Een groepsvak met drie keuzerondjes

Fig 6: Frames with OptionButtons within.

Button (CommandButton)

Buttons or CommandButtons are used to start VBA code (macro's). If you draw a Button from the Forms toolbar on a sheet, Excel will prompt you for a macro to run when the button is clicked (fig 7). If you have not written a macro yet, then you can type the macro's name and click the "New" button to have the (empty) subroutine created for you:

Een macro toewijzen aan een knop

Fig 7: Excel asks you what macro to run when the button is clicked.

If you used the CommandButton from the Control toolbox, you need to double-click the button (in design mode) to access its VBA click event. Code for control toolbox (ActiveX) controls is typically written in the code module behind the sheet they are placed on.

TIP: If you want to change the properties of a control from the control toolbox (an ActiveX control), then you must put your sheet into "Design mode". In Excel 2003 you can click the first button on the control toolbox toolbar. In Excel 2007 and up you can find this button on the "Developer" tab, within the "Controls" group. When you want to start using the controls, click the same button to get out of design mode.


 


Comments

All comments about this page:


Comment by: Divyakanth (1/11/2010 12:17:08 AM)

Hello,

When you add a control to a worksheet say a check box, is it possible to get the corresponding rows on which it is placed?

OR

Is it possible to bind a control to certain no. of rows so that you will be able to know the related rows ?

Thanks for your time in advance.

 


Comment by: Jan Karel Pieterse (1/11/2010 1:29:57 AM)

Hi Divyakanth,

The TopLeftCell property of a control returns the cell which is below the top-left corner of your control. So:

Sub Example()
    MsgBox "Your Control is on top of cell: " & ActiveSheet.CheckBoxes("Check Box 1").TopLeftCell.Address
End Sub

 


Comment by: Divyakanth (1/11/2010 4:15:09 AM)

Thank you very much for your swift response. I was infact in the process of removing the checkboxes. Your response saved them ;-)

 


Comment by: Joseph Powell (1/24/2014 4:42:16 PM)

Is it possible to utilize combo boxes to do dependent drop down lists?

 


Comment by: Jan Karel Pieterse (1/24/2014 6:18:55 PM)

Hi Joseph,

Yes of course. How many dependent lists would you have?

 


Comment by: Ronald van der Sanden (11/22/2017 3:06:40 PM)

Hi
Recently I have downloaded a time sheet with auto hidden scroll bar. Cannot find this type in the defeloper tap. How to get this very usefool bar.
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.

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