VBA Subscript Out Of Range

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. 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.

Code:
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,805
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,272
Messages
5,527,710
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top