Subscript Out of Range Error when printing from array

[Nuke_It_Newport]

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!

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

 

[kanadaaa]

No variable declaration for newArr?

 

[diddi]

hi and welcome to MrExcel.
it would be helpful to know which line the code stops at.
i suspect the error is in this line:
Sheets(newArr).PrintOut Copies:=1, Preview:=True, Collate:=True
because newArr should actually be newArr(i) or whatever loop variable you use. there is really little piont to creating newArr at all unless it is used elsewhere because you could have printed straight from the loop anyway

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

 

[Nuke_It_Newport]

hi and welcome to MrExcel.
it would be helpful to know which line the code stops at.
i suspect the error is in this line:
Sheets(newArr).PrintOut Copies:=1, Preview:=True, Collate:=True
because newArr should actually be newArr(i) or whatever loop variable you use. there is really little piont to creating newArr at all unless it is used elsewhere because you could have printed straight from the loop anyway

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

Thanks. You are correct, the error is in the last line of the code. The "newArr" array is used to eliminate any unselected sheets in the array. I've tried changing the last code line to what you suggested, and I still get the error. I obviously don't understand how to use arrays as well as I need to. My understanding is that I print from an array, with code like "Sheets(Sheet1, Sheet2, Sheet3).PrintOut" I'm trying to loop through the sheets, and print the ones that are selected through the checkboxes in the custom ribbon controls. Am I overcomplicating things for myself?

 

[Nuke_It_Newport]

No variable declaration for newArr?

Good catch! I added Dim newArr() as Variant, but I still get the same error on the last line of the code.

 

[Nuke_It_Newport]

I changed my code a little bit after doing some debugging. Here's the code, along with a screenshot of the output.

'Callback for btns5_btn11 onAction

Sub btns5_btn11_onAction(control As IRibbonControl) 'Print
'   i is counter to run through check boxes
'   J  is array position for selected sheets

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

For i = LBound(shtArr) To UBound(shtArr) 'Number of available sheets

    If shtArr(i) <> "" Then 'Checkbox is checked, sheet is selected
        
        newArr(J) = (shtArr(i))
        Debug.Print ("newArr Position " & J & "=" & shtArr(i))
        J = J + 1 'Go to next newArr Position
    End If
Next i 'Get state of next checkbox
ReDim Preserve newArr(LBound(shtArr) To J)

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

End Sub

Can anybody tell me what I'm doing wrong here? If you look at the screenshot, the code is removing the empty slots from array "shtArr". The array "newArr is the list of selected sheets with the empty slots removed. The yellow line in the screenshot is where the code is throwing an error. I hope this paints a more clear picture of what is going on. Thanks!

 

[diddi]

i think you misunderstood my post...

this is the whole thing

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

 

[RoryA]

You are adding 1 to J after populating the item. That means that at the end, J is 1 more than the index you actually need, so you should actually redim to J - 1. (you had the opposite problem before, in that item 0 was always "")

 

[Nuke_It_Newport]

i think you misunderstood my post...

this is the whole thing

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 sure did... Thanks for clarifying that. I replaced my code completely with yours, and it still errors out on the same step. See attached screenshot. Could there be anything else, such as how I Dim the array (Dim shtArr(6) As Variant)? Thanks for your help!

 

[Nuke_It_Newport]

i think you misunderstood my post...

this is the whole thing

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 sure did... Thanks for clarifying that. I replaced my code completely with yours, and it still errors out on the same step. See attached screenshot. Could there be anything else, such as how I Dim the array (Dim shtArr(6) As Variant)? Thanks for your help!

You are adding 1 to J after populating the item. That means that at the end, J is 1 more than the index you actually need, so you should actually redim to J - 1. (you had the opposite problem before, in that item 0 was always "")

Thank you! This made the output what I would expect it to be, with the blanks removed from the array, but the code still errors on the same line. See screenshot. Any idea what else could be wrong? Thanks!