Subscript Out of Range Error when printing from array

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
47
Office Version
  1. 365
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
 
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: 4
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
is it possible to dropbox link the file
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Can you post a screenshot of the project explorer window with all the sheets showing?
 
Upvote 0
There's the problem. Your array has codenames in it, not the tab names. You should put the tab names into the array.
 
Upvote 0
Solution
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".
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top