UserForm ComboBox Worksheet Reset-CodePudding

this is the first time I've ever worked with VBA UserForms, I've coded with VBA before and have had some experience producing programs that work in the background but not so much with a user interface. I'd really appreciate some help trouble shooting my dilemma. I took a series of screenshots to represent in order of events what is happening.

This is the number of worksheets before running the UserForm

This is the UserForm with the current worksheets

This is the Updated number of worksheets after closing both the Macro and Userform

This is the Userform with the same number of worksheets

As you can see the number of worksheets did not update after running it a second time and the hidden worksheet is displayed as an option.

Private m_Cancelled As Boolean

Public Property Get Cancelled() As Variant
    Cancelled = m_Cancelled
End Property

Private Sub ComboBox1_Change()
    
End Sub

Private Sub CommandButton1_Click()
    Hide
End Sub

Private Sub CommandButton2_Click()
    ' Hide the Userform and set cancelled to true
    Hide
    m_Cancelled = True
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
    ReDim InitialArray(ActiveWorkbook.Worksheets.Count) As Variant
    Dim i As Integer
    For i = 1 To ActiveWorkbook.Worksheets.Count
        InitialArray(i) = ActiveWorkbook.Sheets(i).Name
    Next i
    ComboBox1.List = InitialArray
End Sub

Private Sub UserForm_Activate()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer _
                                        , CloseMode As Integer)
    
    ' Prevent the form being unloaded
    If CloseMode = vbFormControlMenu Then Cancel = True
    
    ' Hide the Userform and set cancelled to true
    Hide
    m_Cancelled = True
    
End Sub

This is the code I'm currently using. It's a bit rough and tumble because I'm still learning how to use it and it's various properties.

I would like the combobox to update when ran a second time if a worksheet is added or removed. I'd also like the conbobox to keep the hidden worksheets hidden from the options.

Again any help would be greatly appreciated! Thank you! :)

CodePudding user response：

Write the Names of Visible Worksheets to a Combo Box

You need an array of visible worksheet names but you don't know how many worksheets are visible. In this case, a better choice is to use a dictionary since its keys (or its items) result in an array.

Private Sub UserForm_Initialize()
    With CreateObject("Scripting.Dictionary")
        Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            If ws.Visible = xlSheetVisible Then .Add ws.Name, Empty
        Next ws
        ComboBox1.List = .Keys
    End With
End Sub

If for some reason you want to stick with the array, you could use the following.

Private Sub UserForm_Initialize()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim wCount As Long: wCount = wb.Worksheets.Count
    Dim wsNames() As String: ReDim wsNames(1 To wCount)

    Dim ws As Worksheet, w As Long

    For Each ws In wb.Worksheets
        If ws.Visible = xlSheetVisible Then
            w = w   1
            wsNames(w) = ws.Name
        End If
    Next ws

    If w < wCount Then ReDim Preserve wsNames(1 To w)

    ComboBox1.List = wsNames

End Sub