I created a macro for Excel which opens a list of all visible sheets in a workbook and goes to the desired sheet as you scroll through the list. The idea is to avoid using the mouse as much as possible.
I am forced to scroll down starting from the first item in the list.
I would like to instead "start" from the initial sheet (wherever it may be) so I can scroll up/down depending on what sheet I would like to open.
In other words,
Code for the listbox:
Private Sub CommandButton1_Click()
Unload ListBox
End Sub
Private Sub UserForm_Initialize()
Dim WS As Worksheet
For Each WS In Worksheets
ListBox1.AddItem WS.Name
Next WS
End Sub
Private Sub ListBox1_Click()
Sheets(ListBox1.Value).Activate
End Sub
Code which opens the listbox:
Public Sub ShowUserForm()
Load ListBox
ListBox.Show
Debug.Print "===="
Debug.Print
End Sub
another one...
Private Sub UserForm_Initialize()
Dim ws As Worksheet, idx As Long
With Me.ListBox1
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
.AddItem ws.Name
If ws Is ActiveSheet Then
idx = .ListCount - 1 ' item indexes start at zero
End If
End If
Next
.ListIndex = idx '
End With
End Sub
Private Sub ListBox1_Change()
Worksheets(ListBox1.Value).Activate
End Sub
You mentioned "all . . . sheets", if you want to include Chart sheets loop Each objSheet in Sheets and in the change event replace Worksheets with Sheets