Nuke_It_Newport
New Member
- Joined
- Nov 17, 2020
- Messages
- 47
- Office Version
- 365
- Platform
- 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!
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