ActiveX Controls

To create an ActiveX command button control, click the top-left button in the ActiveX Controls section of the Controls dialog box, opened from the Insert button on the Developer tab of the Ribbon. When you draw your button in the worksheet, you enter into design mode. When you are in design mode, you can select a control with a left-click and edit it. You must turn off design mode if you want the new control to respond to events. You can do this by clicking the Design Mode button on the Developer tab of the Ribbon so it is no longer highlighted. Figure 1-10 shows the Design Mode button as it appears when design mode is active, after the insertion of the ActiveX control.

p^ id ^ - 1 » KecGFTtenxlsm - Microsoft E*cel

r

1— Heme Insert Page La/cut Formulas

Data Review View

Developer

V

™ <

^ g 'Jit Relative References " ' Visual Macros Insert Basic A Mano Security

Design Mode

" Code Expansion Packs

Source Document ftuntrlslsu ' ! f™* 1 ■— Panel

Cöde

CÓTÍÍTDIS

'ML

Modify

CommandButtonl - f* j =EMBED(rlFcrms.CommandEutton.l"r""}

A | B | C. 1 D 1 E

F

G

H

1 J

K

L

I

Jan Feb Mar Apr

May Jun

2

3

5

Button 1 1

1

6

7

S

o CommandButtonl b

9

u-----TJ b

-

10

U

12

.14.

H

1 1

tí,

Ready

iö 13-

Figure 1-10

You are not prompted to assign a macro to the ActiveX command button, but you do need to write a click-event procedure for the button. An event procedure is a sub procedure that is executed when, for example, you click a button. To do this, make sure you are still in design mode and double-click the command button to open the VBE window and display the code module behind the worksheet. The Sub and End Sub statement lines for your code will have been inserted in the module, and you can add in the code necessary to run the MonthNames2 macro, as shown in Figure 1-11.

Recorder.xlsm - Sheetl (Code]

r. 3Í0

CornmantfButtonl ■ v ¡ Cllch

i

1

Option Explicit

Erivate. Sub CQiaraandSatuonl Zliclc()

Call Mont-íílíaiaesi End Sub

"i

-J

i < 1

9

Figure 1-11

To run this code, switch back to the worksheet, turn off design mode, and click the command button.

If you want to make changes to the command button, you need to return to design mode by clicking the Design Mode button. You can then select the command button and change its size and position on the worksheet. You can also display its properties by right-clicking it and choosing Properties to display the window shown in Figure 1-12.

Properties i_.

Command Button 1 CommandButmn

Alphabetic Categorized

Properties i_.

Command Button 1 CommandButmn

Alphabetic Categorized

(Name)

CommandButtoni

Accelerator

AutoLoad

False

AutoSize .Fate

BackColor

□ QH8QQ00QQF&

Backs tyle

1 - fmBackStyleOpaque

Caption

CommandButton 1

Enabled

¡True

Font

Calibri

ForeColor

■ 3H80Q00012&

Height

25,5

Left

112

Locked

¡True

MoLiselcon

¡(None)

MousePointer

0 - fmMousePomterDefauit

Picture

! (None)

PlcturePosifen

7 - fmPicturePositionAbQUeCenter

Placement

\i

PrintObject

¡True

Shadow

False

i akeFocusünC lick True

Top

¡57

Visible

¡True

Width

mi

WardWrap

False

To change the text on the command button, change the Caption property. You can also set the font for the caption and the foreground and background colors. If you want the button to work satisfactorily in Excel 97, it is a good idea to change the TakeFocusOnClick property from its default value of True to False. If the button takes the focus when you click it, Excel 97 does not allow you to assign values to some properties, such as the NumberFormat property of the Range object.

0 0

Post a comment