Programming Excel with VBA and .NET

Use Controls from the Worksheet Class

A key advantage of the Forms 2.0 controls is that they can interact with the worksheet class. That’s an abstract advantage best illustrated by a short example:

1. Create a new worksheet.

2. Click the SpinButton control on the Control Toolbox and draw the control at the edge of cell B2 on the worksheet, as shown in Figure 20-18.

   

   Figure 20-18. Using a SpinButton to set the value of a cell

3. Click View Code on the Control Toolbox. Excel displays the sheet’s class in the Visual Basic Editor.

4. Enter the following code in the spin button’s Change event procedure (shown in bold):

       Private Sub SpinButton1_Change(  )
           SpinButton1.BottomRightCell.Offset(-1, -1).Value = SpinButton1.Value
       End Sub

5. Return to the worksheet and click Exit Design Mode on the Control Toolbox.

The cool thing about this sample is that if you move the SpinButton to another location, the effect of clicking up or down moves to the adjacent cell. That’s possible because the control is a member of the sheet’s class.

One thing you’ll notice about this sample is that the SpinButton stops spinning at 0. That’s because the control’s Min property is 0 by default. To change that:

1. Click Design Mode on the Control Toolbox.

2. Select the SpinButton control on the worksheet.

3. Click Properties on the Control Toolbox. Excel displays the Properties dialog box (Figure 20-19).

4. Change the Min property to -100 and click Exit Design ...