Microsoft Office Tutorials and References
In Depth Information
14.6.3.2 ActivateSelectedSheet procedure
Unload Me
will fill out the code shell as follows and cause the form to be unloaded when the user hits the
Cancel button:
Private Sub cmdCancel_Click()
Unload Me
End Sub
14.6.3.2 ActivateSelectedSheet procedure
Next, we create a procedure that will activate the selected sheet. We want this procedure to be
called in three situations; namely, when the user:
Selects a sheet name from the list box and clicks the Activate button (or uses the Alt-A
hot key)
Double-clicks on a sheet name in the list box
Selects a sheet name from the list box and hits the Enter key
Since this code will be used in three different situations, we can avoid repeating the code by
placing it in its own procedure in the General section of a UserForm, as shown in Example 14-1 .
Example 14-1. The ActivateSelectedSheet Procedure
Sub ActivateSelectedSheet()
If lstSheets.ListIndex > -1 Then
Sheets(lstSheets.List(lstSheets.ListIndex)).Activate
End If
Unload Me
End Sub
This code demonstrates some list box properties. First, the ListIndex property returns the index
number (starting at 0) of the currently selected item in the list box. Thus, the following code
checks to see if an item is selected (otherwise ListIndex = -1 ):
If lstSheets.ListIndex > -1 Then
The code:
lstSheets.List(i)
returns the i th item in the list box (as a string). Thus:
lstSheets.List(lstSheets.ListIndex))
is the currently selected item—that is, the currently selected sheet name. Finally, the code:
Sheets(lstSheets.List(lstSheets.ListIndex)).Activate
activates that worksheet by invoking its Activate method. We will discuss the Activate method in
Chapter 18 . For now, we simply note that if a worksheet has the name MySheet, then the code:
Sheets("MySheet").Activate
activates that sheet.
 
Search JabSto ::




Custom Search