New VBA Subscript Out Of Range Error

GoCavs

New Member
Joined
Jul 11, 2017
Messages
15
Hello All,

I am trying to select sheets to generate a PDF by using a UserForm with checkboxes so the user can select which sheets he/she wants generated into a PDF. Below is my code and I feel as if I am close but I continue to get a "Run-time error '9': Subscript out of range" at the "Sheets(ary).Select" line near the bottom of my code. Any and all help is appreciated and thank you in advance for your help.

Code:
Private Sub chbxEnter_Click()


Dim PDFsheets As String
    Dim s As Worksheet
    PDFsheets = "Sheet10,Sheet15,Sheet6,Sheet5,Sheet4,Sheet14,Sheet11,Sheet12,Sheet17,Sheet13,Sheet2"
    ary = Split(PDFsheets, ",")


        If CheckBox1.Value = True Then
        PDFsheets = "Sheet10"
        End If
      
        If CheckBox2.Value = True Then
            If PDFsheets = "" Then
            PDFsheets = "Sheet15"
        Else
            PDFsheets = PDFsheets & ",Sheet15"
        End If
        End If
            
        If CheckBox3.Value = True Then
            If PDFsheets = "" Then
            PDFsheets = Sheet6
        Else
            PDFsheets = PDFsheets & ",Sheet6"
        End If
        End If
                  
                  
        If CheckBox4.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet4
        Else
            PDFsheets = PDFsheets & ",Sheet4"
        End If
        End If
                        
        If CheckBox5.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet5
        Else
            PDFsheets = PDFsheets & ",Sheet5"
        End If
        End If
                              
        If CheckBox6.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet14
        Else
            PDFsheets = PDFsheets & ",Sheet14"
        End If
        End If
                              
        If CheckBox7.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet11
        Else
            PDFsheets = PDFsheets & ",Sheet11"
        End If
        End If
        
        If CheckBox8.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet12
        Else
            PDFsheets = PDFsheets & ",Sheet12"
        End If
        End If
        
        If CheckBox9.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet17
        Else
            PDFsheets = PDFsheets & ",Sheet17"
        End If
        End If
        
        If CheckBox10.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet13
        Else
            PDFsheets = PDFsheets & ",Sheet13"
        End If
        End If
        
        If CheckBox11.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet2
        Else
            PDFsheets = PDFsheets & ",Sheet2"
        End If
        End If
        
  
    Sheets(ary).Select
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\TestFolder\Book1.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
                                
                                




End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Make sure all the sheets listed in PDFsheets exist.
 
Upvote 0
ary is a zero-based array that includes all the sheets in the string PDFSheets as you initially defined it -i.e., the line just before ary = Split(...). If one or more of those sheets doesn't exist you will get a subscript out of range error. But, I suspect you don't want all those sheets so you need to define ary after you set up the desired PDFSheets string.
 
Upvote 0
Thank you for your help, I tried to move the "ary = (Split(PDFSheets, ",")" line to below the desired PDFSheets string and I am either getting a compile error or the same subscript out of range error. Is there some place specific I should move the ary line? I'm sorry but I am extremely new to VBA.

Code:
Private Sub chbxEnter_Click()


    Dim PDFsheets As String
    Dim s As Worksheet
    PDFsheets = "Sheet10,Sheet15,Sheet6,Sheet5,Sheet4,Sheet14,Sheet11,Sheet12,Sheet17,Sheet13,Sheet3"
    ary = Split(PDFsheets, ",")
    
        If CheckBox1.Value = True Then
        PDFsheets = "Sheet10"
        End If
      
        If CheckBox2.Value = True Then
            If PDFsheets = "" Then
            PDFsheets = "Sheet15"
        Else
            PDFsheets = PDFsheets & ",Sheet15"
        End If
        End If
            
        If CheckBox3.Value = True Then
            If PDFsheets = "" Then
            PDFsheets = Sheet6
        Else
            PDFsheets = PDFsheets & ",Sheet6"
        End If
        End If
                  
                  
        If CheckBox4.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet4
        Else
            PDFsheets = PDFsheets & ",Sheet4"
        End If
        End If
                        
        If CheckBox5.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet5
        Else
            PDFsheets = PDFsheets & ",Sheet5"
        End If
        End If
                              
        If CheckBox6.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet14
        Else
            PDFsheets = PDFsheets & ",Sheet14"
        End If
        End If
                              
        If CheckBox7.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet11
        Else
            PDFsheets = PDFsheets & ",Sheet11"
        End If
        End If
        
        If CheckBox8.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet12
        Else
            PDFsheets = PDFsheets & ",Sheet12"
        End If
        End If
        
        If CheckBox9.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet17
        Else
            PDFsheets = PDFsheets & ",Sheet17"
        End If
        End If
        
        If CheckBox10.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet13
        Else
            PDFsheets = PDFsheets & ",Sheet13"
        End If
        End If
        
        If CheckBox11.Value = True Then
        If PDFsheets = "" Then
           PDFsheets = Sheet3
        Else
            PDFsheets = PDFsheets & ",Sheet3"
        End If
        End If
        
    Sheets(ary).Select
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\TestFolder\Book1.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
 
Upvote 0
Where exactly in the code should I put the "ary=Split(...)" line... Thank you for your help as I am very new VBA
 
Upvote 0
Where exactly in the code should I put the "ary=Split(...)" line... Thank you for your help as I am very new VBA
After you have the desired sheets captured in the string PDFSheets, then put ary = Split(...). If you are still getting a subscript out of range error, be certain that the sheet names in the code match exactly the tab names in your workbook (no unwanted leading or trailing spaces, for example).
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top