UNHIDE SHEET TO PRINT

[josros60]

Hi,

have listbox listing all sheets and the code below to print them the problem is all sheets are hidden, how to get the to unhide print once print done to hide it again,

here is 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
        
        'If you'd like to print out
        'Sheets(SheetArray()).PrintOut

    End With

End Sub

here the listbox code:

Private Sub Worksheet_Activate()
Dim Sh

    Me.ListBoxSh.Clear

    For Each Sh In ThisWorkbook.Sheets
        Me.ListBoxSh.AddItem Sh.Name
    Next Sh
End Sub

thank you,

 

[Alex Blakenburg]

See if this works for you:
It will need a bit more code if you they are not "all" hidden to start with and we need to store the initial setting and reset it.

Sub Print_Sheets()
    Dim i As Long, c As Long
    Dim SheetArray() As String
    Dim sht As Worksheet
    
    ReDim SheetArray(1 To ActiveWorkbook.Sheets.Count)
    c = 0

    With ActiveSheet.ListBoxSh

        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                c = c + 1
                SheetArray(c) = .List(i)
                Sheets(SheetArray(c)).Visible = True
            End If
        Next i
        
        ReDim Preserve SheetArray(1 To c)

        Sheets(SheetArray()).PrintPreview
        
        For Each sht In Sheets(SheetArray())
            sht.Visible = False
        Next sht
        
        'If you'd like to print out
        'Sheets(SheetArray()).PrintOut
        
    End With

End Sub

 

[josros60]

Worked good.

just two things:

1. every time I print open object format options no sure why
2. is is possible to put the option that the only worksheet no hide after printing because is the master sheet, how can add that option to keep that sheet visible.


thank you so much for all your help.

 

[Alex Blakenburg]

1. every time I print open object format options no sure why

Can you give me a picture of the options box you are talking about ?
And is that only after you uncommented the ".PrintOut" line ?

 

[Alex Blakenburg]

If you want to print without the preview just uncomment the preview line.
I have added to avoid hiding the sheet called Master in the code below. Change the word Master to your sheet name.
However it is only printing and hiding the master when is selected in your list box, so consider whether you want to put an if statement like the one I have around your Worksheet_Activate macro when you load the List box.

Sub Print_Sheets()
    Dim i As Long, c As Long
    Dim SheetArray() As String
    Dim sht As Worksheet
    Dim shtMstrName As String
    
    shtMstrName = "Master"              ' Change to your sheet name for the Master
    
    ReDim SheetArray(1 To ActiveWorkbook.Sheets.Count)
    c = 0

    With ActiveSheet.ListBoxSh

        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                'ReDim Preserve SheetArray(c)
                c = c + 1
                SheetArray(c) = .List(i)
                Sheets(SheetArray(c)).Visible = True
            End If
        Next i
        
        ReDim Preserve SheetArray(1 To c)
        'If you want to run PrintPreview
        'Sheets(SheetArray()).PrintPreview
        
        'If you'd like to print out
        Sheets(SheetArray()).PrintOut
        
        For Each sht In Sheets(SheetArray())
            If sht.Name <> shtMstrName Then ' Do not hide the Master if selected
                sht.Visible = False
            End If
        Next sht
        
    End With

End Sub

 

[josros60]

 

[Alex Blakenburg]

Hmm, there is nothing in the code you started with or in my modified code that would cause that to happen.
Is there more code to it that you actually running (look for the Shapes word in the code) ?

You need to be running a line that looks something like this for it to happen:

ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select

 

[josros60]

Wow, worked fantastic.

thank you so much for all your help.

 

[Alex Blakenburg]

No problem. Glad I could help & thanks for the feedback.

 

[ES407]

If you want to print without the preview just uncomment the preview line.
I have added to avoid hiding the sheet called Master in the code below. Change the word Master to your sheet name.
However it is only printing and hiding the master when is selected in your list box, so consider whether you want to put an if statement like the one I have around your Worksheet_Activate macro when you load the List box.

Sub Print_Sheets()
    Dim i As Long, c As Long
    Dim SheetArray() As String
    Dim sht As Worksheet
    Dim shtMstrName As String
   
    shtMstrName = "Master"              ' Change to your sheet name for the Master
   
    ReDim SheetArray(1 To ActiveWorkbook.Sheets.Count)
    c = 0

    With ActiveSheet.ListBoxSh

        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                'ReDim Preserve SheetArray(c)
                c = c + 1
                SheetArray(c) = .List(i)
                Sheets(SheetArray(c)).Visible = True
            End If
        Next i
       
        ReDim Preserve SheetArray(1 To c)
        'If you want to run PrintPreview
        'Sheets(SheetArray()).PrintPreview
       
        'If you'd like to print out
        Sheets(SheetArray()).PrintOut
       
        For Each sht In Sheets(SheetArray())
            If sht.Name <> shtMstrName Then ' Do not hide the Master if selected
                sht.Visible = False
            End If
        Next sht
       
    End With

End Sub

I know this is an old post but i want to say thank you! It seems i was using the exact ssame listbox and print button as OP and having the exact same issue with printing my hidden sheets. Your code worked flawlessly. Thank you so much!!!