Print sheets based on Check Box value

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have prepared below code to print only certain sheets in a workbook based on the ticked checkboxes in a userform. But, I'm getting a "Run Time Error 9: Subscript out of Range" in the below highlighted purple line. Please advise if there is any way to fix this error.

VBA Code:
Function uniqueArr(ParamArray myArr() As Variant) As Variant()
    Dim dict As Object
    Dim V As Variant, W As Variant
    Dim I As Long

Set dict = CreateObject("Scripting.Dictionary")
For Each V In myArr 'loop through each myArr
    For Each W In V 'loop through the contents of each myArr
        If Not dict.exists(W) Then dict.Add W, W
    Next W
Next V
uniqueArr = dict.keys
End Function

Sub pdf()
   
    Dim PDFfile As String
    Dim CurrentPrinterNe As String, PrinterNe As String
    Dim wksAllSheets As Variant
    Dim wksSheet1 As Worksheet
    Dim wb As Workbook
    Dim arr1, arr2, arr3, arr4, arr5
   
    Set wb = ActiveWorkbook
   
UserForm5.Show
If UserForm5.CheckBox1.Value = True Then
arr1 = Array("Cover")
Else
arr1 = Array("")
End If
If UserForm5.CheckBox2.Value = True Then
arr2 = Array("Revision")
Else
arr2 = Array("")
End If
If UserForm5.CheckBox3.Value = True Then
arr3 = Array("Emerson COMMERCIAL OFFER")
Else
arr3 = Array("")
End If
If UserForm5.CheckBox4.Value = True Then
arr4 = Array("Technical Offer")
Else
arr4 = Array("")
End If

wksAllSheets = uniqueArr(arr1, arr2, arr3, arr4)

PDFfile = wb.path & "\" & wb.Worksheets(1).Range("F12").Value & ".pdf"
[COLOR=rgb(85, 57, 130)]wb.Sheets(wksAllSheets).Select[/COLOR]
Set wksSheet1 = wb.Sheets("Cover")
    wksSheet1.ExportAsFixedFormat _
              Type:=xlTypePDF, _
              Filename:=PDFfile, _
              Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, _
              IgnorePrintAreas:=False, _
              OpenAfterPublish:=False
wb.Worksheets(1).Select

End Sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,273
Office Version
  1. 2010
Platform
  1. Windows
One (or more) of the sheet names differ from what is being specified in the code.
 

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
One (or more) of the sheet names differ from what is being specified in the code.
Hi,

Please can you elaborate. I'm not able to find the source of this error. Or, please can you provide a corrected code.
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,155
Office Version
  1. 2010
@Tanyaann1995

Hi

actually I'm not at good in vba ,but I think you have sheet name is existed in your code doesn't match with sheet name in your file .

so I suggest to use coping the sheets names from inside the file and paste in your code. . not writing just copy & paste . somtimes the sheets names contains for spaces . that's why shows the error
I hope this help you
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,273
Office Version
  1. 2010
Platform
  1. Windows
I set up a test workbook in order to test your code.
In doing so, to name the sheets the same as what you have in your code, I copied what's between the double quotes and pasted each of those as the name on the sheet tabs and your code ran without getting that error.
In order to reproduce the error you get, I inserted an extra space into one of the sheet names on the tabs.
Hence, why I say.... the sheet names differ from what is being specified in the code.

Something such as an extra space character at the beginning or end of the name showing on the sheet tab is very difficult to spot visually.
I'd suggest you do as I did and copy the names from the code to rename your sheets.

Good luck with the project
NoSparks
 
Solution

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
I set up a test workbook in order to test your code.
In doing so, to name the sheets the same as what you have in your code, I copied what's between the double quotes and pasted each of those as the name on the sheet tabs and your code ran without getting that error.
In order to reproduce the error you get, I inserted an extra space into one of the sheet names on the tabs.
Hence, why I say.... the sheet names differ from what is being specified in the code.

Something such as an extra space character at the beginning or end of the name showing on the sheet tab is very difficult to spot visually.
I'd suggest you do as I did and copy the names from the code to rename your sheets.

Good luck with the project
NoSparks
Thanks, it worked now :)
 

Forum statistics

Threads
1,147,734
Messages
5,742,862
Members
423,760
Latest member
photogfrog

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
Top