Print Hidden Worksheets from Userform

[nathandavies]

Hi All,
I have some code which i created a few years ago, but i'm wanting to change it so that it only shows and prints certain worksheets which would be hidden. at the minute the code just prints any active worksheets.

this is the code.

Sub PrintSheet()

    Dim i As Integer
    Dim TopPos As Integer
    Dim SheetCount As Integer
    Dim PrintDlg As DialogSheet
    Dim CurrentSheet As Worksheet
    Dim cb As CheckBox
    Application.ScreenUpdating = False


'   Check for protected workbook
    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical
        Exit Sub
    End If


'   Add a temporary dialog sheet
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add


    SheetCount = 0


'   Add the checkboxes


    TopPos = 40
    For i = 1 To ActiveWorkbook.Worksheets.Count
        Set CurrentSheet = ActiveWorkbook.Worksheets(i)
'       Skip empty sheets and hidden sheets
        If Application.CountA(CurrentSheet.Cells) <> 0 And _
            CurrentSheet.Visible Then
            SheetCount = SheetCount + 1
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
                PrintDlg.CheckBoxes(SheetCount).Text = _
                    CurrentSheet.Name
            TopPos = TopPos + 13
        End If
    Next i


'   Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240


'   Set dialog height, width, and caption
    With PrintDlg.DialogFrame
        .Height = Application.Max _
            (68, PrintDlg.DialogFrame.Top + TopPos - 34)
        .Width = 230
        .Caption = "Select sheets to print"


    End With


'   Change tab order of OK and Cancel buttons
'   so the 1st option button will have the focus
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront


'   Display the dialog box
    CurrentSheet.Activate
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
        If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    Worksheets(cb.Caption).Activate
                    ActiveSheet.PrintOut
'                   ActiveSheet.PrintPreview 'for debugging


                End If
            Next cb
        End If
    Else
        MsgBox "All worksheets are empty."
    End If


'   Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False
    PrintDlg.Delete


End Sub

[offthelip]

have you tried changing:

 CurrentSheet.Visible Then

to

Not( CurrentSheet.Visible) Then

[nathandavies]

I have multiple worksheets hidden, i only want to select a few of them.

[offthelip]

How are you going to select them??

[nathandavies]

the userform has check boxes

[MINCUS1308]

I might be wrong but I'm pretty sure you cannot print hidden sheets maybe try something along the lines of this:
application.screenupdating = false
if UserFormCheckBox Then Sheet#.visible= true
sheet#.print
Sheet#.visible= false
application.screenupdating = true

[MINCUS1308]

This slight modification will allow your hidden sheets to appear on your userform.
wonderful code BTW.

For i = 1 To ActiveWorkbook.Worksheets.Count
        Set CurrentSheet = ActiveWorkbook.Worksheets(i)
         '       Skip empty sheets and hidden sheets
        checkme = CurrentSheet.Visible
        CurrentSheet.Visible = True
        If Application.CountA(CurrentSheet.Cells) <> 0 And _
        CurrentSheet.Visible Then
            SheetCount = SheetCount + 1
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
            PrintDlg.CheckBoxes(SheetCount).Text = _
            CurrentSheet.Name
            TopPos = TopPos + 13
        End If
        CurrentSheet.Visible = checkme
    Next i

[MINCUS1308]

And this will allow you to print

 For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    Worksheets(cb.Caption).Visible = True
                    Worksheets(cb.Caption).Activate
                    ActiveSheet.PrintOut
                    Worksheets(cb.Caption).Visible = False
                     '                   ActiveSheet.PrintPreview 'for debugging
                     
                     
                End If
            Next cb

[nathandavies]

Mincus1308,
That has allowed me to print of hidden worksheets perfectly.

Once i have printed my workbook always moves onto the last worksheet tab, how would i stop that i want to keep it on a specific worksheet.

[MINCUS1308]

When you say 'specific worksheet' do you always mean sheet1? or like the last active worksheet?

the reason that this is happening is your For loop counts through all your worksheets (from the first to the last)
and sets 'CurrentSheet = ActiveWorkbook.Worksheets(I)' (so the last sheet in the workbook will be held in the variable CurrentSheet)
and near the end of execution you asked to activate 'CurrentSheet' (activating the sheet held by the variable)
So the code above will always terminate with the last worksheet selected

Something to keep in mind - I'm pretty sure you cannot 'activate' or 'select' a worksheet if it is hidden.

there are a number of different ways you can activate or select a worksheet but
If you know what worksheet you would like to end on you can add this near the end of you code:
Sheet#.Visible = True
Sheet#.Activate

If you know the worksheet name or code name and need more help with this problem just reply and ill show you where to place it.

[nathandavies]

the worksheet name is always "summary" and its the first sheet in the workbook

[MINCUS1308]

Ok.
Sheet names can almost always be changed and VBA is a little relentless when it comes to the finer points of grammar.
I recommend using the worksheets code name which cannot be changed as easily.

if you look at the little picture you can see that I have a worksheet named "Sheet 1" and it has the code name Sheet1.
I can call the worksheet using any of the following methods:
1) Sheet1.Select (Using Code Name)
2) Worksheets("Sheet 1").Select (Using the Worksheet Name)


Example.jpg


Assuming that you worksheet named "summary" is Code named Sheet1 I would sub in the following code:

 '   Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False
    PrintDlg.Delete
     'SET USERS FOCUS TO WORKSHEET "summary"
     Sheet1.Visible = True
     Sheet1.Select
     
End Sub

[MINCUS1308]

Nathandavies, Did that work?

[nathandavies]

Yes, I have run multiple tests this morning.

Thanks for you help on this!