Error 9 - Subscript out of range selecting sheets from UserForm 3

[Storyteller]

Hello All,
I have a form that allows the users to select (via checkbox) which sheets they would like to combine into one pdf output. As my title says I am getting an "Error 9 - subscript out of range" error. Any help would be greatly appreciated.

Private Sub cmdPDF_Click()
    'On Error Resume Next

    Dim strPDF As String
    Dim ClientName As String
    Dim ClientFilename As Variant

    ClientName = Range("Client_First") & Range("Client_LasT")
    
    'Non Reg Client
    If Me.chkNonRegClient = True Then
        strPDF = strPDF & """Non_Reg_Client""" & ", "
    End If
    'Non Reg Spouse
    If Me.chkNonRegSpouse = True Then
        strPDF = strPDF & """Non_Reg_Spouse""" & ", "
    End If
    'TFSA Client
    If Me.chkTFSAClient = True Then
        strPDF = strPDF & """TFSA_Client""" & ", "
    End If
    'TFSA Spouse
    If Me.chkTFSASpouse = True Then
        strPDF = strPDF & """TFSA_Spouse""" & ", "
    End If
    'RRSP/RRIF Client
    If Me.chkRRSPRRIFClient = True Then
        strPDF = strPDF & """RRSP_RRIF_Client""" & ", "
    End If
    'RRSP/RRIF Spouse
    If Me.chkRRSPRRIFSpouse = True Then
        strPDF = strPDF & """RRSP_RRIF_Spouse""" & ", "
    End If
    'Locked/LIF Client
    If Me.chkLockedLIFClient = True Then
        strPDF = strPDF & """Locked_LIF_Client""" & ", "
    End If
    'Locked/LIF Spouse
    If Me.chkLokcedLIFSpouse = True Then
        strPDF = strPDF & """Locked_LIF_Spouse""" & ", "
    End If
    'Investment Account Summary
    If Me.chkIAS = True Then
        strPDF = strPDF & """InvestmentAccountSummary""" & ", "
    End If
    'Deposit/Withdrawal Summary
    If Me.chkDWDSummary = True Then
        strPDF = strPDF & """Dep_WD_Summary"""
    End If
    
    
    'Save selected worksheets as a PDF
    ClientFilename = Application.GetSaveAsFilename( _
    InitialFileName:=ClientName, _
    filefilter:="PDF, *.pdf", _
    Title:="Save As PDF")

    Sheets(Array(strPDF)).Select
    Selection.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ClientFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
    
End Sub

 

[strongm]

Seems to me that there are situations where strPDF will have a hanging China, which is likely to host things.

 

[Storyteller]

Hello strongm,
That could be the case. However, when I select items and include the Dep_WD_Summary option this is what I get in the immediate window:

? strpdf
"Non_Reg_Client", "TFSA_Client", "InvestmentAccountSummary", "Dep_WD_Summary"

Given the above I am not sure why Sheets(Array(strPDF)).Select does not give me the same results as the following.

When I record the macro here is what I see:

    Sheets(Array("Non_Reg_Client", "TFSA_Client", "InvestmentAccountSummary", _
        "Dep_WD_Summary")).Select
    Sheets("Non_Reg_Client").Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Desktop\ClientName.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False

Been banging my head against the desk for some time trying to figure this one out.

 

[combo]

[tt]Array[/tt] converts list into array, as you need to pass array as argument. [tt]Sheets(Array(strPDF))[/tt] statement has single element array containing whole string (you can test Array(strPDF) with LBbound, UBound and Array(strPDF)(0) ). No such sheet, so the error.
Use [tt]Sheets(Split(strPDF,", "))[/tt] instead (or "," and join sheet names without space). Remove double quotes from the string.

combo

 

[mintjulep]

Seems a waste of time and effort to build a comma separated list then use split to blow it apart into array elements.

Why not just directly populate an array and pass that to sheets(myArray).select

 

[Storyteller]

Hello mintjulep,
I agree . . but, I am working within my current skillset and "directly populate an array" is what is eluding me.
I have done a variation of the above code with:

dim strPDF(9) as Variant

If me.chkNonRegClient = True Then
[indent]strPDF(0) = "Non_Reg_Client"[/indent]
End if

If me.chkNonRegSpouse = True Then
[indent]strPDF(1) = "Non_Reg_Spouse"[/indent]
End if

continuing until:

Sheets(Array(strPDF()).Select

I still get the same Error 9 - Subscript out of Range error.

I am stumped at how to directly populate the array based on checkbox values from a userform. Any assistance would be greatly appreciated.

 

[Storyteller]

Hello combo,
I will have to try Sheets(Split(strPDF,",")) when I get back to my production computer to test this out. I will respond with the results later.

 

[mintjulep]

As a starting point.

Option Explicit
Option Base 0

Public Sub CommandButton1_Click()

    Dim Count As Integer
    
    Dim ChkboxArray() As Variant
    ChkboxArray = Array("Checkbox1", "Checkbox2", "Checkbox3")
    
    Dim SheetsNameArray() As Variant
    SheetsNameArray = Array("Sheet1", "Sheet2", "Sheet3")
    
    Dim SelectionCollection As New Collection
     
    For Count = LBound(ChkboxArray) To UBound(ChkboxArray)
        If Me.Controls(ChkboxArray(Count)) Then SelectionCollection.Add (SheetsNameArray(Count))
    Next Count
  
    Sheets(CollectionToArray(SelectionCollection)).Select

End Sub

Function CollectionToArray(C As Collection) As String()

    Dim A() As String
    ReDim Preserve A(C.Count - 1)

    Dim i As Integer

    For i = 1 To C.Count

        A(i - 1) = C.Item(i)

    Next

    CollectionToArray = A

End Function

 

[combo]

Revise strPDF structure, no double quotes inside, if space inside sheet's name then the name in single quotation marks, as in external references in formulas. If you use single "," in Split function, remove spaces after commas in strPDF.

combo

 

[strongm]

Check the spelling of the sheet names exactly matches what you are putting in the array

Basically, if any of the array items doesn't match exactly an existing sheet name, then you'll get the subscript error. Which is (one of) the reasons why your most recent example code does notgeneraly work, since you dim it with 10 elements, but don't necessarily populate all those elements. We can fix this with only a minor refactoring of your code, something like:

[blue]    Dim strPDF() As String
    ReDim Preserve strPDF(0) As String

    If Me.chkNonRegClient = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    
    If Me.chkNonRegSpouse = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
   [COLOR=green] '...
    '...
    '...[/color]
    ReDim Preserve strPDF(UBound(strPDF) - 1)
    Sheets(strPDF).Select [COLOR=green]'NOT Sheets(Array(strPDF()).Select[/color][/blue]

This will achieve much the same affect as mintjulip's collection method - an array that ONLY has the correct number of elements, each with a sheetname in it.

Another alternative would be to group all the CheckBoxes into a Frame, and set each of their tags to the relevant sheetname, and then do something like the following:

[blue]Private Sub CommandButton1_Click()
    Dim myctrl As Control
    Dim strPDF() As String
    ReDim strPDF(9) As String
    Dim itemcount As Long
    
    For Each myctrl In Frame1.Controls
        If TypeName(myctrl) = "CheckBox" Then
            If myctrl.Value = True Then
                strPDF(itemcount) = myctrl.Tag
            End If
            itemcount = itemcount + 1
        End If
       
    Next
    
    ReDim Preserve strPDF(itemcount - 1) As String
    Sheets(strPDF).Select
End Sub[/blue]

 

[mintjulep]

Store sheet names and control names in a hidden sheet to remove hard-coded configuration information.
Better align control names and sheet names to condense the table to one column.
Generate checkboxes on the UserForm on the fly.

 

[combo]

Generate checkboxes on the UserForm on the fly.

Actually listbox with ListStyle set to 1 (fmListStyleOption) and MultiSelect set to 1 (fmMultiSelectMulti) can be used instead, not nice list, but scrollable and without runtime controls. Second hidden column or separate array can be used to store sheet names.

Before printing to pdf I would check if the user checked anything to print to, basing on the initial post (can be easily replaced by array and loop, as mintjulep and strongm suggested):

Dim SelReplace As Boolean
bSelReplace = True ' no prior selection, start new
If Me.chkNonRegClient = True Then Sheets("Non_Reg_Client").Select bSelReplace: bSelReplace = False
If Me.chkNonRegSpouse = True Then Sheets("Non_Reg_Spouse").Select bSelReplace: bSelReplace = False
' etc.
If bSelReplace Then
    MsgBox "Nothing to print selected"
Else
    SelectedSheets.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ClientFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
End If

combo

 

[Storyteller]

Hello All,
I have reworked my code as per strongm's suggestion:

    'Non Reg Client
    If Me.chkNonRegClient = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Non Reg Spouse
    If Me.chkNonRegSpouse = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
' ---
' ---
    Sheets(strPDF).Select
    Sheets(strPDF(LBound(strPDF))).Activate '<--- I had to add this as the pdf output was only the strPDF array number, not the values on the sheet
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ClientFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

combo: your suggestion of Sheets(Split(strPDF)).Select should have worked (I did additional research on this methodology) but I was still getting "Error 9"
mintjulep: I need to spend more time developing my working knowledge of Array's to be able to fully understand and appreciate your suggestions.

Thank you to everyone who took the time to offer their knowledge and expertise.

 

[combo]

You should have proper structure of strPDF string. So you should build it with (assuming it will be splitted with single comma, without space: ","):
[tt]strPDF = strPDF & iif(Len(strPDF)=0,"",",") &"Non_Reg_Client"[/tt]
I.e. commas only between sheet names, no space after comma, no quotation marks around sheet names.

From your initial code I expected that you need to export in general multiple sheets. In the accepted code you activate first worksheet of the selection and export it. If the selection contains multiple sheets and you plan to process them all, then instead of [tt]Sheets(strPDF(LBound(strPDF))).Activate[/tt] and next code line, use the code between [tt]Else[/tt] and [tt]End If[/tt] in my example


combo

 

[Storyteller]

Hello combo;
Your comment about checking to see if anything was selected before exporting makes sense. So, I followed your example and wrote the following:

Sub ExportToPDF()
    'On Error Resume Next

    Dim strPDF As String
    Dim bSelReplace As Boolean
    Dim ClientName As String
    Dim ClientFilename As Variant

    bSelReplace = True
    ClientName = Range("Client_First") & Range("Client_LasT")

    If Me.chkNonRegClient = True Then Sheets("Non_Reg_Client").Select bSelReplace: bSelReplace = False
    If Me.chkNonRegSpouse = True Then Sheets("Non_Reg_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkTFSAClient = True Then Sheets("TFSA_Client").Select bSelReplace: bSelReplace = False
    If Me.chkTFSASpouse = True Then Sheets("TFSA_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkRRSPRRIFClient = True Then Sheets("RRSP_RRIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkRRSPRRIFSpouse = True Then Sheets("RRSP_RRIF_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkLockedLIFClient = True Then Sheets("Locked_LIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkLokcedLIFSpouse = True Then Sheets("Locked_LIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkIAS = True Then Sheets("InvestmentAccountSummary").Select bSelReplace: bSelReplace = False
    If Me.chkDWDSummary = True Then Sheets("Dep_WD_Summary").Select bSelReplace: bSelReplace = False
 
    'Save selected worksheets as a PDF
    ClientFilename = Application.GetSaveAsFilename( _
    InitialFileName:=ClientName, _
    filefilter:="PDF, *.pdf", _
    Title:="Save As PDF")
    
    If bSelReplace Then
        MsgBox "Nothing to print selected"
    Else
        SelectedSheets.ExportAsFixedFormat _ '<----- Broke here with Error 424: Object Required
            Type:=xlTypePDF, _
            Filename:=ClientFilename, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    End If
    
    Unload Me
    
End Sub

I was able to see that the sheets that I had selected from the form were in fact selected, so I am not sure why the error.

 

[Storyteller]

Hello combo;
Using the Split() and iif(), I wrote the following:

Sub ExportToPDFString()
    Dim strPDF As String
    Dim ClientName As String
    Dim ClientFilename As Variant

    ClientName = Range("Client_First") & Range("Client_LasT")
    
    'Non Reg Client
    If Me.chkNonRegClient = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "Non_Reg_Client"
    End If
    'Non Reg Spouse
    If Me.chkNonRegSpouse = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "Non_Reg_Spouse"
    End If
    'TFSA Client
    If Me.chkTFSAClient = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "TFSA_Client"
    End If
    'TFSA Spouse
    If Me.chkTFSASpouse = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "TFSA_Spouse"
    End If
    'RRSP/RRIF Client
    If Me.chkRRSPRRIFClient = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "RRSP_RRIF_Client"
    End If
    'RRSP/RRIF Spouse
    If Me.chkRRSPRRIFSpouse = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "RRSP_RRIF_Spouse"
    End If
    'Locked/LIF Client
    If Me.chkLockedLIFClient = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "Locked_LIF_Client"
    End If
    'Locked/LIF Spouse
    If Me.chkLokcedLIFSpouse = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "Locked_LIF_Spouse"
    End If
    'Investment Account Summary
    If Me.chkIAS = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "InvestmentAccountSummary"
    End If
    'Deposit/Withdrawal Summary
    If Me.chkDWDSummary = True Then
        strPDF = strPDF & IIf(Len(strPDF) = 0, "", ",") & "Dep_WD_Summary"
    End If
    
    
    'Save selected worksheets as a PDF
    ClientFilename = Application.GetSaveAsFilename( _
    InitialFileName:=ClientName, _
    filefilter:="PDF, *.pdf", _
    Title:="Save As PDF")

    Sheets(Split(strPDF, ",")).Select
    Selection.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ClientFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
    Unload Me
    
End Sub

It did in fact export to a PDF, but only the array numbers were visible on the pdf. The information on the sheets was not there. I can see the sheets that I selected from the form were in fact selected.

 

[combo]

It should work if you have sheets selected and call [tt]ExportAsFixedFormat[/tt] for any single worksheet.
In the first example, for instance:
[tt]ActiveWindow.SelectedSheets(1).ExportAsFixedFormat _[/tt]
In the second one:
[tt]Sheets(Split(strPDF, ",")).Select
Sheets(Split(strPDF, ","))(1).ExportAsFixedFormat _[/tt]
where Sheets(Split(strPDF, ","))(1) is just to have quick reference to a worksheet, you can easily find a better one.

combo

 

[Storyteller]

Hello All,
I truly appreciate all the help and feedback to my question. I have taken combo's suggestion and wrote the following:

Sub ExportToPDFBoolean()
'December 2019: I like this one as it is clean and short, and I can explain how it works :)
    On Error Resume Next

    Dim strPDF As String
    Dim bSelReplace As Boolean
    Dim ClientName As String
    Dim ClientFilename As Variant

    bSelReplace = True
    ClientName = Range("Client_First") & Range("Client_LasT")
    
    'Checks to see which checkboxes have been selected (True)
    If Me.chkNonRegClient = True Then Sheets("Non_Reg_Client").Select bSelReplace: bSelReplace = False
    If Me.chkNonRegSpouse = True Then Sheets("Non_Reg_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkTFSAClient = True Then Sheets("TFSA_Client").Select bSelReplace: bSelReplace = False
    If Me.chkTFSASpouse = True Then Sheets("TFSA_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkRRSPRRIFClient = True Then Sheets("RRSP_RRIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkRRSPRRIFSpouse = True Then Sheets("RRSP_RRIF_Spouse").Select bSelReplace: bSelReplace = False
    If Me.chkLockedLIFClient = True Then Sheets("Locked_LIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkLokcedLIFSpouse = True Then Sheets("Locked_LIF_Client").Select bSelReplace: bSelReplace = False
    If Me.chkIAS = True Then Sheets("InvestmentAccountSummary").Select bSelReplace: bSelReplace = False
    If Me.chkDWDSummary = True Then Sheets("Dep_WD_Summary").Select bSelReplace: bSelReplace = False
 
    
    If bSelReplace Then
        MsgBox "Need to select one item to export to PDF", , "Nothing selected"
        Exit Sub
    Else
        'Save selected worksheets as a PDF
        ClientFilename = Application.GetSaveAsFilename( _
        InitialFileName:=ClientName, _
        filefilter:="PDF, *.pdf", _
        Title:="Save As PDF")
        
        'Export to PDF
        ActiveWindow.SelectedSheets(1).ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=ClientFilename, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    End If
    
    Unload Me
    
End Sub

Thank you. It works and it has the added benefit of checking to see if the user has actually selected something to export to PDF.

 

[Storyteller]

Hello All,
In the interest of helping other people who are searching for a similar solution. Here is the code to ExportToPDF using an Array.

Sub ExportToPDFArray()
    'December 2019: Uses an Array to collect which sheets have been selected to be exported to PDF
    Dim strPDF() As String
    Dim ClientName As String
    Dim ClientFilename As Variant
    
    'Resizes array
    ReDim Preserve strPDF(0) As String

    ClientName = Range("Client_First") & Range("Client_Last")
    
    'This resized the array to include the new value added
    'ReDim Preserve strPDF(UBound(strPDF) + 1)

    'Non Reg Client
    If Me.chkNonRegClient = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Non Reg Spouse
    If Me.chkNonRegSpouse = True Then
        strPDF(UBound(strPDF)) = "Non_Reg_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'TFSA Client
    If Me.chkTFSAClient = True Then
        strPDF(UBound(strPDF)) = "TFSA_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'TFSA Spouse
    If Me.chkTFSASpouse = True Then
         strPDF(UBound(strPDF)) = "TFSA_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'RRSP/RRIF Client
    If Me.chkRRSPRRIFClient = True Then
        strPDF(UBound(strPDF)) = "RRSP_RRIF_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'RRSP/RRIF Spouse
    If Me.chkRRSPRRIFSpouse = True Then
        strPDF(UBound(strPDF)) = "RRSP_RRIF_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Locked/LIF Client
    If Me.chkLockedLIFClient = True Then
        strPDF(UBound(strPDF)) = "Locked_LIF_Client"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Locked/LIF Spouse
    If Me.chkLokcedLIFSpouse = True Then
        strPDF(UBound(strPDF)) = "Locked_LIF_Spouse"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Investment Account Summary
    If Me.chkIAS = True Then
        strPDF(UBound(strPDF)) = "InvestmentAccountSummary"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If
    'Deposit/Withdrawal Summary
    If Me.chkDWDSummary = True Then
        strPDF(UBound(strPDF)) = "Dep_WD_Summary"
        ReDim Preserve strPDF(UBound(strPDF) + 1)
    End If

    'Save selected worksheets as a PDF
    ClientFilename = Application.GetSaveAsFilename( _
    InitialFileName:=ClientName, _
    filefilter:="PDF, *.pdf", _
    Title:="Save As PDF")

    ReDim Preserve strPDF(UBound(strPDF) - 1)

    Sheets(strPDF).Select
    Sheets(strPDF(LBound(strPDF))).Activate
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ClientFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False


    Unload Me

End Sub

Once again, thank you to strongm for his help with the above code.