Subscript Out of Range Error when printing from array

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
Hey everyone-
I have a workbook with a custom ribbon. When checkbox controls in the ribbon corresponding to weekdays are checked, the code is supposed to add the sheet names to an array, skipping the non selected sheets. When the Print button in the ribbon is clicked, It is then supposed to print the sheets from the array. It is giving me a "Subscript Out Of Range" error when Sub btns5_btn11_onAction is run. Here's the code.
Can anyone see anything obviously wrong with my code? Thanks!

VBA Code:
Dim shtArr(6) As Variant

'Callback for chkBoxMon onAction 'Monday Check Box
Sub chkBoxMon_onAction(control As IRibbonControl, MonSel As Boolean) 'Monday
If MonSel Then
    shtArr(0) = "Sheet1"
    Debug.Print (shtArr(0) & " Selected")
Else
    shtArr(0) = ""
     Debug.Print ("Sheet 1 Not Selected")
End If
End Sub

'Callback for chkBoxTue onAction 'Tuesday Check Box
Sub chkBoxTue_onAction(control As IRibbonControl, TueSel As Boolean) 'Tuesday
If TueSel Then
    shtArr(1) = "Sheet2"
    Debug.Print (shtArr(1) & " Selected")
Else
    shtArr(1) = ""
    Debug.Print ("Sheet 2 Not Selected")
End If
End Sub

'Callback for chkBoxWed onAction 'Wednesday Check Box
Sub chkBoxWed_onAction(control As IRibbonControl, WedSel As Boolean) 'Wednesday
  If WedSel Then
    shtArr(2) = "Sheet3"
    Debug.Print (shtArr(2) & " Selected")
Else
    shtArr(2) = ""
    Debug.Print ("Sheet 3 Not Selected")
End If

End Sub

'Callback for chkBoxThu onAction 'Thursday Check Box
Sub chkBoxThu_onAction(control As IRibbonControl, ThuSel As Boolean) 'Thursday
  If ThuSel Then
    shtArr(3) = "Sheet4"
    Debug.Print (shtArr(3) & " Selected")
Else
    shtArr(3) = ""
    Debug.Print ("Sheet 4 Not Selected")
End If
  
End Sub

'Callback for chkBoxFri onAction 'Friday Check Box
Sub chkBoxFri_onAction(control As IRibbonControl, FriSel As Boolean) 'Friday
  If FriSel Then
    shtArr(4) = "Sheet5"
    Debug.Print (shtArr(4) & " Selected")
Else
    shtArr(4) = ""
    Debug.Print ("Sheet 5 Not Selected")
End If
  
End Sub

'Callback for chkBoxSE onAction 'Standby Electricians Check Box
Sub chkBoxSE_onAction(control As IRibbonControl, SESel As Boolean) 'Standby Electricians
 If SESel Then
    shtArr(5) = "Sheet6"
    Debug.Print (shtArr(5) & " Selected")
Else
    shtArr(5) = ""
    Debug.Print ("Sheet 6 Not Selected")
End If

End Sub

'Callback for chkBoxSS onAction 'Standby Supervisor Check Box
Sub chkBoxSS_onAction(control As IRibbonControl, SSSel As Boolean) 'Standby Supervisors
  If SSSel Then
    shtArr(6) = "Sheet7"
    Debug.Print ("Sheet 7 Selected")
Else
    shtArr(6) = ""
    Debug.Print (shtArr(6) & " Not Selected")
End If
End Sub

Sub btns5_btn11_onAction(control As IRibbonControl) 'Print Button on Ribbon

ReDim newArr(LBound(shtArr) To UBound(shtArr))

For i = LBound(shtArr) To UBound(shtArr)

    If shtArr(i) <> "" Then
        J = J + 1
        newArr(J) = shtArr(i)
    End If
Next i
ReDim Preserve newArr(LBound(shtArr) To J)

Sheets(newArr).PrintOut Copies:=1, Preview:=True, Collate:=True

End Sub
 

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
i think you misunderstood my post...

this is the whole thing
VBA Code:
Sub btns5_btn11_onAction(control As IRibbonControl) 'Print
'   i is counter to run through check boxes
    For i = LBound(shtArr) To UBound(shtArr)
        If shtArr(i) <> "" Then
            Sheets(shtArr(i)).PrintOut Copies:=1, Preview:=True, Collate:=True
        End If
    Next i
End Sub
I forgot to post the screenshot...:rolleyes:
 

Attachments

  • Excel Screenshot 2.png
    Excel Screenshot 2.png
    105.8 KB · Views: 3

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,302
Office Version
  1. 2010
Platform
  1. Windows
is it possible to dropbox link the file
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
PrintOut errors out when
1. the target worksheet is hidden OR
2. the name of the target printer is wrong OR
3. the target worksheet is protected OR
4. something else

I doubt if it’s 2.
 

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
13
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

PrintOut errors out when
1. the target worksheet is hidden OR
2. the name of the target printer is wrong OR
3. the target worksheet is protected OR
4. something else

I doubt if it’s 2.
That's good to know. Thanks! I don't have any hidden or protected sheets. I haven't worked in Excel for about 5 years now. I've been teaching myself Access and SQL for another personal project. That's a different animal, for sure. I seem to remember having protected sheets in a workbook years ago. I had to add code to unprotect the desired sheets, print, then re protect them. If it was a printer issue, would "print preview" code cause the same error? I ask because I tried that instead of PrintOut.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Can you post a screenshot of the project explorer window with all the sheets showing?
 

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
13
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Can you post a screenshot of the project explorer window with all the sheets showing?
Here you go.
 

Attachments

  • Excel Screenshot 4.png
    Excel Screenshot 4.png
    55.3 KB · Views: 5

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
There's the problem. Your array has codenames in it, not the tab names. You should put the tab names into the array.
 
Solution

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
You used the sheet property wrong...
The code must look like
VBA Code:
Sheets("Monday")
The code below
VBA Code:
Sub test()
    Sheets("Sheet1").Activate
End Sub
doesn't work because there's no sheet named "Sheet1".
 

Watch MrExcel Video

Forum statistics

Threads
1,130,037
Messages
5,639,678
Members
417,104
Latest member
Nelsini

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