Set default selections on a List Box on Workbook_Open()

[sparky2205]

Hi folks,
I have a listbox that populates with a list of worksheet names when the workbook is opened.
The user then selects worksheets from the list to have visible or hidden.
What I'm trying to achieve is when the workbook is closed an reopened the users last selections in the listbox are retained.
This is the code I've been trying:

Private Sub Workbook_Open()

Dim N As Long
    For N = 2 To ActiveWorkbook.Sheets.Count - 1
        Sheets("Setup").ListBox1.AddItem ActiveWorkbook.Sheets(N).Name
        If ActiveWorkbook.Sheets(N).Visible = False Then
            Sheets("Setup").ListBox1.List(N).Selected = True
        End If
    Next N
    Sheets("Setup").ListBox1.Height = Sheets("Setup").ListBox1.ListCount * 12

End Sub

It returns the error: "Could not get the List property. Invalid property array index."
The code works fine without the IF statement, i.e. the ListBox is populated correctly with the list of worksheet names.
I think I'm missing an array here somewhere but I'm not sure how to implement it.

 

[Domenic]

First, the Selected property belongs to the ListBox object. Secondly, the indexing for the Selected property starts at zero. Therefore, try the following instead...

Private Sub Workbook_Open()

Dim N As Long
    For N = 2 To ActiveWorkbook.Sheets.Count - 1
        Sheets("Setup").ListBox1.AddItem ActiveWorkbook.Sheets(N).Name
        If ActiveWorkbook.Sheets(N).Visible = False Then
            With Sheets("Setup").ListBox1
                .Selected(.ListCount - 1) = True
            End With
        End If
    Next N
    Sheets("Setup").ListBox1.Height = Sheets("Setup").ListBox1.ListCount * 12

End Sub

However, your macro can be re-written as follows...

Private Sub Workbook_Open()

    Dim N As Long
 
    With ThisWorkbook
        For N = 2 To .Sheets.Count - 1
            .Sheets("Setup").ListBox1.AddItem .Sheets(N).Name
            If .Sheets(N).Visible = False Then
                With .Sheets("Setup").ListBox1
                    .Selected(.ListCount - 1) = True
                End With
            End If
        Next N
    End With
 
   
    With ThisWorkbook.Sheets("Setup").ListBox1
        .Height = .ListCount * 12
    End With

End Sub

Hope this helps!

 

[sparky2205]

Good morning Domenic,
apologies for the tardy response, I was off work for a few days.
Your solution works beautifully.
And thank you for this "First, the Selected property belongs to the ListBox object. Secondly, the indexing for the Selected property starts at zero."
All these little nuggets of explanation are very valuable to me.

 

[Domenic]

That's great, I'm happy to help.

And thanks for your feedback.

Cheers!