Microsoft Office Tutorials and References
In Depth Information
14.6.4 Trying the Activate Utility
Private Sub UserForm_Initialize()
' Fill lstSheets with the list of sheets
Dim cSheets As Integer
Dim i As Integer
cSheets = Sheets.Count
lstSheets.Clear
For i = 1 To cSheets
lstSheets.AddItem Sheets(i).Name
Next
End Sub
This code first gets the total number of sheets (worksheets and charts) in the current workbook.
(We will discuss this in detail in later chapters, so don't worry about it now.) The list box is then
cleared of any previous content. Then we have a For loop that adds the sheet names to the list box.
This is done using the ListBox control's AddItem method. The name of a sheet is given by its
Name property.
14.6.4 Trying the Activate Utility
If all has gone well, you can now save SRXUtils as an add-in, load it through the Tools menu (if it
is currently loaded, you will need to unload it before saving the add-in or Excel will complain),
and try out the new ActivateSheet feature.
14.7 ActiveX Controls on Worksheets
As you may know, ActiveX controls (and standard Excel controls) can be placed directly on a
worksheet. Care must be taken, however, not to clutter up a worksheet with controls that would be
better placed on a UserForm. When only a small number of controls are required, placing these
controls directly on a worksheet may be appropriate.
There are some special considerations when controls are placed directly on a worksheet. In
particular, each ActiveX control on a worksheet (not on a UserForm) is represented by an
OLEObject in the Excel object model. However, it is important to note that OLEObject objects
can also represent embedded OLE objects. Thus, for instance, if we insert a bitmap on a worksheet
(select Object from Excel's Insert menu), this bitmap object will be represented by an OLEObject.
The Worksheet object has a property called OLEObjects that returns the OLEObjects collection
consisting of all OLEObject objects on the worksheet. Thus, the OLEObjects collection for the
active worksheet is:
ActiveSheet.OLEObjects
Because OLEObjects also represent embedded OLE objects (such as bitmaps), we cannot be
certain that, say:
ActiveSheet.OLEObjects(1)
is a control. Thus, it is wise when adding a control or embedded OLE object to a worksheet to
immediately assign the control or object a name and then refer to it by this name rather than by
index, as in:
 
Search JabSto ::




Custom Search