CODEWITHSUNDEEP
excelvbauser-inputmultipleselection
Mike AlFaver Favro
Mike AlFaver Favro

Reputation: 41

VBA Excel - Select multiple sheets with input form

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

Upvotes: 1

Views: 124

Answers (1)

CLR
CLR

Reputation: 12279

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

Upvotes: 2

Related Questions