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.

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:

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 it's 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 2010 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 ;-)

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.