Programming Excel with VBA and .NET

Controls on a Worksheet Versus Controls on a Form

Using controls on a worksheet is a little different from using them on a form. For one thing, you don’t need to create an instance of the worksheet since it already exists. Your code starts running as soon as you exit Design mode and click on the control.

Also, fewer controls are available in the worksheet Control Toolbox. If you want to use a control not found on the Control Toolbox, click the More Controls button, then choose the control from the list shown in Figure 20-20.

Another difference is that controls on a worksheet controls don’t support all of their properties. Specifically, ControlSource, ControlTipText, TabIndex, and TabStop aren’t available for controls on a worksheet.

Finally, you can’t copy controls from a form in the Visual Basic Editor onto a worksheet. If you want to re-create a form as a worksheet, you must redraw the controls manually. In fact, Figure 20-21 shows the Stock History sample implemented as a worksheet rather than a form.

Since I used the same control names as the original sample, I could copy the code from the form class with only two changes:

• Deleted Me.Unload from cmdViewChart_Click

• Added Me.Activate to cmdGetHistory_Click

See the sample workbook for the full code.

Figure 20-20. Using controls not found on the Control Toolbox

Figure 20-21. The Stock History sample as a worksheet