VBA Subscript Out Of Range

[GoCavs]

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. There are 2 sheets "Cover" and "PP" that must be included anytime a user wants to generate 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". Please help as I am slowing losing my grip on reality.

Private Sub chbxEnter_Click()


Dim SheetsFound()
  ReDim SheetsFound(0)
  For i = 1 To 11
    If Me.Controls("CheckBox" & i).Value = True Then
      SheetsFound(UBound(SheetsFound)) = Me.Controls("CheckBox" & i).ControlTipText
      ReDim Preserve SheetsFound(UBound(SheetsFound) + 1)
    End If
  Next i
  ReDim Preserve SheetsFound(UBound(SheetsFound) - 1)
  
        If CheckBox1 = True Then
        Sheets("Approval Form").Visible = True
        Else
        End If
      
        If CheckBox2 = True Then
        Sheets("Business Plan").Visible = True
        Else
        End If
            
        If CheckBox3 = True Then
        Sheets("Deal Worksheet").Visible = True
        Else
        End If
                  
        If CheckBox4 = True Then
        Sheets("Deal Recap").Visible = True
        Else
        End If
                        
        If CheckBox5 = True Then
        Sheets("All Manager Deal Recap").Visible = True
        Else
        End If
                              
        If CheckBox6 = True Then
        Sheets("MEC Dealership Profile").Visible = True
        Else
        End If
                              
        If CheckBox7 = True Then
        Sheets("Loyal").Visible = True
        Else
        End If
        
        If CheckBox8 = True Then
        Sheets("Mid Loyal").Visible = True
        Else
        End If
        
        If CheckBox9 = True Then
        Sheets("Non Loyal").Visible = True
        Else
        End If
        
        If CheckBox10 = True Then
        Sheets("Projected Incentive Report").Visible = True
        Else
        End If
        
        If CheckBox11 = True Then
        Sheets("MEC").Visible = True
        Else
        End If
        
  
  Sheets(SheetsFound).Select


                                Sheets(Array("Cover", "PP", "SheetsFound")).Select
                                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, ignoreprintareas _
                                :=False, openafterpublish:=True
                                






End Sub

 

[StephenCrump]

You didn't say which line is generating the error, but if your code is getting this far, it looks like this line will generate a Subscript out of Range error:

Sheets(Array("Cover", "PP", "SheetsFound")).Select

You have a variable array called SheetsFound that contains sheet names, not a sheet named "SheetsFound" ?

One way you could fix this is to add "Cover" and "PP" to your SheetsFound array, then use:

Sheets(SheetsFound).Select