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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Make sure all the sheets listed in PDFsheets exist.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,217
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

GoCavs

New Member
Joined
Jul 11, 2017
Messages
15
Just double checked and all the sheets listed in the PDFSheets do exist
 

GoCavs

New Member
Joined
Jul 11, 2017
Messages
15

ADVERTISEMENT

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
 

GoCavs

New Member
Joined
Jul 11, 2017
Messages
15
Where exactly in the code should I put the "ary=Split(...)" line... Thank you for your help as I am very new VBA
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,217
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,109,275
Messages
5,527,721
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top