Toggle Button

A ToggleButton control has two states: on and off. Clicking the button toggles between these two states, and the button changes its appearance. Its value is either True (pressed) or False (not pressed). I never use this control because I think a CheckBox is much clearer.

Using Controls on a Worksheet

Many of the UserForm controls can be embedded directly into a worksheet. These controls are accessible by using Excel's Developer Controls Insert command. Adding such controls to a worksheet requires much less effort than creating a UserForm. In addition, you may not have to create any macros because you can link a control to a worksheet cell. For example, if you insert a CheckBox control on a worksheet, you can link it to a particular cell by setting its LinkedCell property. When the CheckBox is checked, the linked cell displays TRUE. When the CheckBox is unchecked, the linked cell displays FALSE.

The accompanying figure shows a worksheet that contains some ActiveX controls. This workbook, named® activex worksheet controls. xlsx, is available on the companion CD-ROM. The workbook uses linked cells and contains no macros.

J,

ft

4

n

t

Morlinur Leon

1

[¡«DHAI

J

PiBiJra: Plr^:

if*.**

Ulhwd

1

r 1 ->- * lh# il.gR 'rim

(llJi Ifl^H

T

r m " m

smcu tun

f U^U'-ird

t U 1J

^ iVvvr'Td

INS »fW naiF iv/ht

11

loir JmHiiil'

Ivwn

Ul* ttfutv-

14

FALSE lEMMip"

D

4] J

| -^Jif |

TFtE JO^lJiTWi

It

■ r

H M4»th» Piymwt:

1

,hi

A-Sri

U

1

i ' i

rj J"

< i

■ ■

Adding controls to a worksheet can be a bit confusing because controls can come from two sources: ■ Form controls: These controls are insertable objects.

■ ActiveX controls: These controls are a subset of those that are available for use on UserForms.

You can use the controls from either of these sources, but it's important that you understand the distinctions between them. The Form controls work much differently than the ActiveX controls.

When you add an ActiveX control to a worksheet, Excel goes into design mode. In this mode, you can adjust the properties of any controls on your worksheet, add or edit event handler procedures for the control, or change its size or position. To display the Properties window for an ActiveX control, use the

Developer Controls Properties command.

For simple buttons, I often use the Button control from the Form controls because it lets me attach any macro to it. If I use a CommandButton control from the ActiveX controls, clicking it will execute its event handler procedure (for example, CommandButton1_Click) in the code module for the Sheet object - you can't attach just any macro to it.

When Excel is in design mode, you can't try out the controls. To test the controls, you must exit design mode by clicking the Developer

Controls

Design mode button (which is a toggle).

4 PREV

NEXT

+1 -1

Post a comment