Microsoft Office Tutorials and References
In Depth Information
14.6.3.3 Activate button code
Finally, the last thing done in the cmdActivate_Click event is to unload the form, since it is no
longer needed.
14.6.3.3 Activate button code
To set the code behind the Activate button, select cmdActivate in the Objects drop-down box
(above the upper-left corner of the code window) and select Click in the Procedures drop-down
box (above the upper-right corner of the code window). You can now fill in the code for the Click
event of the cmdActivate button:
Private Sub cmdActivate_Click()
ActivateSelectedSheet
End Sub
14.6.3.4 Double-click lstSheets code
We also want ActivateSelectedSheet to be called when the user double-clicks on a sheet
name. The DblClick event for the list box fires when the user double-clicks on an item in the list
box. Select lstSheets in the Objects drop-down and DblClk in the Procedures drop-down. Then fill
in the DblClk event code shell:
Private Sub lstSheets_DblClick(ByVal Cancel As _
MSForms.ReturnBoolean)
ActivateSelectedSheet
End Sub
14.6.3.5 Enter key event
We also want to invoke ActivateSelectedSheet when the user selects a sheet name and hits
the Enter key. When the list box has the focus, any keystroke fires the KeyDown event. Choose
this event in the Procedures drop-down and add the code shown in Example 14-2 to the event shell.
Example 14-2. The lstSheets_KeyDown Event Procedure
Private Sub lstSheets_KeyDown(ByVal KeyCode As _
MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then ActivateSelectedSheet
End Sub
In this case, we must add code to determine whether the Enter key was struck. Fortunately, Excel
will fill in the KeyCode parameter of the KeyDown event with the key code for the key that
caused the event to be fired. (For a list of key codes, check "KeyCode" in the Excel VBA help file.)
14.6.3.6 Fill the lstSheets list box
Next, we need to fill the lstSheets list box with a list of all of the sheets in the current
workbook. We want this to be done automatically, so we will place the required code in the
Initialize event of the UserForm. This event is fired by Excel when the form is loaded, but before
it becomes visible. As the name implies, it is designed to initialize various properties of the form
and its controls.
Select UserForm in the Object drop-down and Initialize in the Procedures drop-down. You should
get the UserForm_Initialize event code shell. Fill it with the code shown in Example 14-3 .
Example 14-3. The UserForm_Initialize Event Procedure
 
 
Search JabSto ::




Custom Search