Programming Excel with VBA and .NET

Embed Controls

Embedded form controls let you get input from the user through standard controls like text boxes, command buttons, listboxes, and so on. They are handy for collecting values that populate ranges of cells or to simply get and display values in something other than a grid.

To embed a control on a worksheet:

1. Choose View → Toolbars → Control Toolbox to display the Controls Toolbox.

2. Click the control to add and then click and drag on the worksheet to draw the control as shown in Figure 10-3.

3. Excel embeds the control on the worksheet.

4. Click the Properties button to edit the control’s appearance.

5. Click the Code button to add an event procedure for the control.

6. Click the Design button when finished to switch out of design mode.

You can link embedded controls to values entered in cells . For example, to link the text box in Figure 10-3 to cell A3, set its LinkCell property to $A$3. Now changes to the TextBox update cell A3 and vice versa (Figure 10-4).

Figure 10-3. Adding form controls to a worksheet

Figure 10-4. Linking cells to controls through the LinkedCell property

To create an event procedure for a control so it responds to user actions:

1. Click the Design button to enter design mode.

2. Select the control.

3. Click the View Code button. Excel opens the Visual Basic Editor and creates an event ...