Microsoft Office Tutorials and References
In Depth Information
14.7.2 Adding a Control to a Worksheet Programmatically
Private Sub CommandButton1_Click
Range("A:A").Sort Key1:=Range("A:A")
End Sub
(We will discuss the sort method in Chapter 19 . Don't worry about that now.) This is one
disadvantage of placing controls directly on worksheets.
Of course, one way to avoid this problem is to activate another object before calling the sort
method. For instance, we can amend the previous code as follows:
Private Sub CommandButton1_Click
Range("A:A").Activate
Range("A:A").Sort Key1:=Range("A:A")
CommandButton1.Activate ' Optional
End Sub
It is also worth mentioning that if you save an Excel 97 or Excel 2000 workbook in Excel 5.0/95
Workbook file format, all ActiveX control information will be lost.
14.7.2 Adding a Control to a Worksheet Programmatically
To programmatically add an ActiveX control to a worksheet, we use the Add method of the
OLEObjects collection. The syntax is:
OLEObjectCollection .Add( ClassType, FileName, Link, DisplayAsIcon, _
IconFileName, IconIndex, IconLabel, Left, Top, Width, Height )
The ClassType parameter is the so-called programmatic identifier (or ProgID) for the control.
Table 14-5 shows the ProgIDs for various controls.
Table 14-5. ProgIDs for ActiveX Controls
Control
ProgID
CheckBox
Forms.CheckBox.1
ComboBox
Forms.ComboBox.1
CommandButton
Forms.CommandButton.1
Frame
Forms.Frame.1
Image
Forms.Image.1
Label
Forms.Label.1
ListBox
Forms.ListBox.1
MultiPage
Forms.MultiPage.1
OptionButton
Forms.OptionButton.1
ScrollBar
Forms.ScrollBar.1
SpinButton
Forms.SpinButton.1
TabStrip
Forms.TabStrip.1
TextBox
Forms.TextBox.1
ToggleButton
Forms.ToggleButton.1
The only other parameters that are relevant to adding ActiveX controls (this method is used for
other types of OLE objects as well) are the Left, Top, Width, and Height parameters, which
specify in points the location (with respect to the upper-left corner of cell A1) and size of the
control. All other parameters should be omitted. (This is a good place for named arguments!)
 
 
Search JabSto ::




Custom Search