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
There's the problem. Your array has codenames in it, not the tab names. You should put the tab names into the array.
Holy cow. That was it! I don't know why I never thought of that. :unsure::oops: Thanks!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
13
Office Version
  1. 2013
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".
Thank you! You and RoryA are right! I don't know why I never thought of that. :unsure::oops: Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,032
Messages
5,639,646
Members
417,102
Latest member
bcselect

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