VBA Express : Excel - Listbox to Display and Go To Worksheets

Excel

Listbox to Display and Go To Worksheets

Ease of Use

Easy

Version tested with

2000, 2002

Submitted by:

Zack Barresse

Description:

Displays a list of worksheets, and allows the user to choose one, then to navigate to it.

Discussion:

You may have lots of worksheets in your file, but you don't like right-clicking on the sheet tab scroll arrows, or your users just don't know how. Use this code in your application to help users navigate through a multiple-sheet file.

Code:

instructions for use

			 'Userform code:
Private Sub CommandButton1_Click() 
    Dim i As Integer, sht As String 
    For i = 0 To ListBox1.ListCount - 1 
        If ListBox1.Selected(i) = True Then 
            sht = ListBox1.List(i) 
        End If 
    Next i 
    Sheets(sht).Activate 
    End 
End Sub 
 
Private Sub CommandButton2_Click() 
    Unload UserForm1 
End Sub 
 
Private Sub UserForm_Initialize() 
    Dim ws As Worksheet 
    For Each ws In ThisWorkbook.Worksheets 
        ListBox1.AddItem (ws.Name) 
    Next ws 
End Sub 
 
 'Worksheet Code:
Private Sub CommandButton1_Click() 
    Load UserForm1 
    UserForm1.Show 
End Sub

How to use:

Open your file and hit Alt+F11 to open the Visual Basic Editor (VBE).
Hit Insert-UserForm.
Design a form like the one in the sample file and then right-click it in the left-hand side of the code window, and choose View Code.
Paste the UserForm code above into the code window at the right.
Now, on the left-hand side, double-click the worksheet from which you wish to navigate.
Paste the Worksheet code into the code window at right.
Close the VBE.
Go to the Worksheet in which you pasted the code.
Place a command button on it using the Control Toolbox.
Get out of design view.
Save the file.

Test the code:

Click the Command button, choose a folder, and hit GO.

Sample File:

Navigator.zip 11.92KB

Approved by mdmackillop

This entry has been viewed 417 times.