Print button for a hidden sheet

[supermanjohn01]

I want to put a print button on my sheet entitled "Contract Quoter", but when click it I want it to print another sheet that is hidden entitled "Contract Invoice," (only printing the set print area on the "Contract Invoice" sheet). I have created the button using shapes, but can't get any codes I've found or created to perform like I want it. Does anyone have a code or suggestions? Thanks.

 

[rollis13]

If you have already set the print area and the active printer, why wouldn't something like this do the job ?

Option Explicit
Sub test()
    With Sheets("Contract Invoice")
        .Visible = True
        .PrintOut
        .Visible = False
    End With
End Sub

 

[rlv01]

Another.

Sub Macro1()
' Print hidden worksheet
    Dim HiddenState As Variant

    With Worksheets("Contract Invoice")
        Application.ScreenUpdating = False
        HiddenState = .Visible
        .Visible = xlSheetVisible
        .PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        .Visible = HiddenState
        Application.ScreenUpdating = True
    End With
End Sub

 

[supermanjohn01]

BOTH of these worked!!! Thank you, thank you, thank you!!!

 

[rollis13]

Yes, they act exactly the same way because they are the same even if the second has more lines of code.
Thanks for the positive feedback, I'm glad we were able to help.
By the way, you probably need to mark this thread as 'Solved'.

 

[rlv01]

The mechanism is the same, but how it used is slightly different. With respect to the worksheet .visible property, the underlying data type is Long because there are three visibility states: visible, hidden, and very hidden. The type Long values for those states are is -1,0 and 2 respectively. That is:

Sub TestA()
    With Sheets("Contract Invoice")
        'Without enumeration
        .Visible = -1                                 'sheet is visible
        .Visible = 0                                  'sheet is hidden
        .Visible = 2                                  'sheet is 'VeryHidden' (which means the user cannot use the Excel menu to unhide)

        'With enumeration
        .Visible = xlSheetVisible                     'sheet is visible
        .Visible = xlSheetHidden                      'sheet is hidden
        .Visible = xlSheetVeryHidden                  'sheet is 'VeryHidden (which means the user cannot use the Excel menu to unhide)
    End With
End Sub

@rollis13 's code example is using a boolean True and False as a quick shorthand for unhide and hide. That works because typecasting True & False Boolean to type Long yields -1 & 0 respectively. It is a quick and dirty method for hiding/unhiding when you know what you want. It has the advantage of being fast as simple and I have used it myself.

The main wrinkle in the example I posted is to first preserve the as-found .visible property to a temporary variable (HiddenState). That way it does not matter if the sheet I am unhiding was Hidden or VeryHidden, because after printing, it will be returned to the original hidden state.

 

[supermanjohn01]

The formula did exactly what I asked for it to do. BUT... is there something in the macro formulas for the button where I can get it to update the "Contract Invoice" page before it prints it? I just added a filter to it so it won't print the blank cells, but the filter has to be refreshed for the new information to show and be printed. Any suggestions?

 

[rlv01]

I don't know what 'update' means to you. However you should be able to use the Excel macro recorder to record your actions when you manually perform an update/refresh and either add the macro recorder code to your print macro or post it here and we can look at how best to do it.