How do I set up macro in VBA to choose worksheets?

[Guest]

I want to set up a macro that will prompt me to select the name of a
worksheet (within a workbook) to view and then display that sheet.

 

[KL]

OK, here you go!
1. Create a user-form (UserForm1) in the VBA Editor.
2. On this user-form place a list-box (ListBox1) and two command-buttons
(CommandButton1 and CommandButton2).
3. Double-click on the user-form to open its code window and copy the
following code into it:

'-----------Code Start--------------
Private Sub CommandButton1_Click()
' This is going to be your Cancel button
'You may want to change its Caption property to Cancel
Unload Me
End Sub

Private Sub CommandButton2_Click()
' This is going to be your OK button
'You may want to change its Caption property to OK
On Error Resume Next
ThisWorkbook.Sheets(ListBox1.Text).Activate
Unload Me
End Sub

Private Sub UserForm_Initialize()
' This populates your list-box with available sheets names
For Each s In ThisWorkbook.Sheets
ListBox1.AddItem s.Name
Next s
End Sub
'-----------Code Finish--------------

4. In the Workbook module insert the following code:

'-----------Code Start--------------
Private Sub Workbook_Open()
UserForm1.Show
End Sub
'-----------Code Finish--------------

5. Save the file.

Regards,
KL

 

[Bob Phillips]

Take look at this earlier post http://tinyurl.com/6rqt2


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[alanad]

I used this method to create custom buttons for my workbook, but th
userform that displays the button choices appears infront of whateve
the last worksheet was that was open the last time the workbook wa
used. Is it possible to have the userform appear infront of a blan
page or screen when I open the workbook