How to get a list box to initialize at a desired starting point in the list?-CodePudding

I have 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

However, whenever I use the macro, I am forced to scroll down (starting from the first item in the list). I would like my macro instead 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,

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

Here is the current 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

And here is the code which opens the listbox:

Public Sub ShowUserForm()
    Load ListBox
    ListBox.Show
    Debug.Print "===="
    Debug.Print
End Sub

Could you please advise on how to approach this? Thank you so much.

Have a nice day!

CodePudding user response：

what's about that:

Private Sub UserForm_Initialize()
 Dim wksTab As Worksheet

 For Each wksTab In ThisWorkbook.Worksheets

  If wksTab.Visible = xlSheetVisible Then
   If wksTab.Name <> ActiveSheet.Name Then
     Me.ListBox1.AddItem wksTab.Name
   End If
  End If

 Next wksTab
 Me.ListBox1.AddItem ActiveSheet.Name
 Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1

End Sub

Best regards Bernd

CodePudding user response：

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