I am running a ListBox that shows hidden sheets in a workbook with the VBA code:
Private Sub ListBoxSh_Click()
End Sub
Private Sub Worksheet_Activate()
Dim sh
Me.ListBoxSh.Clear
For Each sh In ThisWorkbook.Sheets
If sh.Visible = xlSheetVisible Then
Me.ListBoxSh.AddItem sh.Name
End If
Next sh
End Sub
And a Print button with the code:
Sub Print_Sheets()
Dim i As Long, c As Long
Dim SheetArray() As String
With ActiveSheet.ListBoxSh
For i = 0 To .ListCount - 1
If .Selected(i) Then
ReDim Preserve SheetArray(c)
SheetArray(c) = .List(i)
c = c + 1
End If
Next i
Sheets(SheetArray()).PrintPreview
End With
End Sub
This all works fine. My issue is that I am trying to change this to print my hidden sheets. By changing "If sh.Visible = xlSheetVisible Then" to "If sh.Visible = xlSheetHidden Then" in the ListBox VBA, I get the results I am looking for (ONLY hidden sheets populate the ListBox), but when I hit the print button, I get: "Run-time error '1004': PrintPreview method of Sheets class failed"
any ideas anyone?
Private Sub ListBoxSh_Click()
End Sub
Private Sub Worksheet_Activate()
Dim sh
Me.ListBoxSh.Clear
For Each sh In ThisWorkbook.Sheets
If sh.Visible = xlSheetVisible Then
Me.ListBoxSh.AddItem sh.Name
End If
Next sh
End Sub
And a Print button with the code:
Sub Print_Sheets()
Dim i As Long, c As Long
Dim SheetArray() As String
With ActiveSheet.ListBoxSh
For i = 0 To .ListCount - 1
If .Selected(i) Then
ReDim Preserve SheetArray(c)
SheetArray(c) = .List(i)
c = c + 1
End If
Next i
Sheets(SheetArray()).PrintPreview
End With
End Sub
This all works fine. My issue is that I am trying to change this to print my hidden sheets. By changing "If sh.Visible = xlSheetVisible Then" to "If sh.Visible = xlSheetHidden Then" in the ListBox VBA, I get the results I am looking for (ONLY hidden sheets populate the ListBox), but when I hit the print button, I get: "Run-time error '1004': PrintPreview method of Sheets class failed"
any ideas anyone?