Search code examples
excelvbalistbox

How to initialize a listbox to a desired starting point in the list?


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.

Here is a screenshot of how it looks

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,

  1. I would like the listbox to populate with all visible sheets
  2. I would like the starting point for the user to be the active sheet so they can scroll up/down from their starting point

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

Solution

  • 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