Trying to Print Multiple Sheets from user selection in Form Checkboxes in Excel VBA-CodePudding

So I have a form called "Print_Form" that has 20 checkboxes that upon form initialization take on the sheet names of the first 20 sheets of my workbook.

(no issue with the UserForm_Initialize() sub, this works fine)

Private Sub UserForm_Initialize()
    CheckBox1.Caption = Sheets(1).Name
    CheckBox2.Caption = Sheets(2).Name
    CheckBox3.Caption = Sheets(3).Name
    CheckBox4.Caption = Sheets(4).Name
    CheckBox5.Caption = Sheets(5).Name
    CheckBox6.Caption = Sheets(6).Name
    CheckBox7.Caption = Sheets(7).Name
    CheckBox8.Caption = Sheets(8).Name
    CheckBox9.Caption = Sheets(9).Name
    CheckBox10.Caption = Sheets(10).Name
    CheckBox11.Caption = Sheets(11).Name
    CheckBox12.Caption = Sheets(12).Name
    CheckBox13.Caption = Sheets(13).Name
    CheckBox14.Caption = Sheets(14).Name
    CheckBox15.Caption = Sheets(15).Name
    CheckBox16.Caption = Sheets(16).Name
    CheckBox17.Caption = Sheets(17).Name
    CheckBox18.Caption = Sheets(18).Name
    CheckBox19.Caption = Sheets(19).Name
    CheckBox20.Caption = Sheets(20).Name
End Sub

Where I am running into issues is in the following sub routine when the user clicks the print button in the form. The intention behind this button is to print all the sheets that the user has selected (i.e. the sheets that had their corresponding checkbox checked by the user). Currently, when I select multiple checkboxes and then click on the print button I get the following error; "Run-Time error '9': Subscript out of range.

Private Sub cmdPrint_Click()

    Dim i As Integer
    Dim cb As MSForms.Control
    Dim SheetArray() As String
        
    i = 0
    'Search form for a checkbox
    For Each cb In Me.Controls
    i = i   1
    ReDim Preserve SheetArray(i)
        'If the control is a checkbox
        If TypeName(cb) = "CheckBox" Then
        'and the checkbox is checked
            If cb.Value = True Then
            'Add the sheet to the sheet array (sheet name string was already added to the checkbox property caption; see UserForm_initialize)
            SheetArray(i) = cb.Caption
            End If
        End If
    Next cb
    'Print Sheet Array
    Sheets(SheetArray()).PrintOut
    
    Unload Me
End Sub

If anyone has any ideas that would help me get this to work I would be very appreciative. Thank you in advance. :)

CodePudding user response：

Try this:

Private Sub UserForm_Initialize()
    Dim i As Long
    For i = 1 To 20 'less typing....
        Me.Controls("CheckBox" & i).Caption = Sheets(i).Name
    Next i
End Sub

Private Sub cmdPrint_Click()
    Dim i As Integer, s As String, sep
    For i = 1 To 20
        With Me.Controls("CheckBox" & i)
            If .Value Then
                s = s & sep & .Caption
                sep = "," 'add delimiter after first item
            End If
        End With
    Next i
    Sheets(Split(s, ",")).PrintOut
    Unload Me
End Sub