VBA Excel - Select multiple sheets with input form

Question

I'm writing a macro to visualize an input form with all sheets present in the active workbook and then the user needs to select all that is to be copied.

I wrote the code for the form with the sheets displayed.

I can't find the solution to store the selection done by the user in an array (??), or use it for the sheet selection (and future copy) when the "Done" button as pressed.

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
  
    For Each ws In Worksheets
        ws.Activate
        ListBox1.AddItem ActiveSheet.Name
    Next
    
    ListBox1.MultiSelect = fmMultiSelectMulti
    
End Sub

Answer 1

You should alter your code to only list visible worksheets, to prevent errors trying to select hidden sheets.

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
  
    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible Then ListBox1.AddItem ws.Name
    Next
    
    ListBox1.MultiSelect = fmMultiSelectMulti
    
End Sub

The below assumes your button is called DONE. You may need to change that.

Private Sub Done_Click()

    Dim arrSheetlist()

    X = 1
    For i = 0 To ListBox1.ListCount - 1
       If ListBox1.Selected(i) = True Then
            ReDim Preserve arrSheetlist(1 To X)
            arrSheetlist(X) = ListBox1.List(i)
            X = X + 1
       End If
    Next i
   
    If X > 1 Then Worksheets(arrSheetlist).Select
  
End Sub