VB Excel Before Print Routine with mutiple printing

user_5157154 0
Viewed 43

In Excel, I wrote this code to run a particular routine each time the Print Button is pressed. It changes the sheet color to white and changes it back after printing. 

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False

' change color to white
Range("A6:BD53").Interior.ColorIndex = 0

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

' AfterPrint_ change color back
 Range( _
        "G6:BD6,G8:AD8,AI8:BD8,AN11:AO11,Y11:Z11,F11:V11,K13:AK13,Q14:AK14,J15:T15,P38:P39"). _
        Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Range( _
        "AB15:AE15,AU14:AX14,AU15:AX15,AU16:AX16,AA17:AC17,N24:W24,E28:O32,R28:U28,X28:AB31,AV37:BD37,V40:AL40,P41:U42,V43:AL43,V45:AL45,AV43:BD44" _
        ).Select
    Range("AV43").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Application.EnableEvents = True
End Sub

It works just fine if I print each sheet singularly, but doesn't if I try to print many sheets at once, i.e. if I select more sheets at one time and print them. In this case, the routine is run only for the first sheet being printed and not for the others. Anyone knows why and what I can do to correct it?

printing vba excel
1 Answers

Try this use of a For loop to go through all the selected sheets:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
On Error GoTo Err
Application.EnableEvents = False

Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets

    ' change color to white
    ws.Range("A6:BD53").Interior.ColorIndex = 0

    ws.PrintOut Copies:=1, Collate:=True
    ' AfterPrint_ change color back
    With ws.Range("G6:BD6,G8:AD8,AI8:BD8,AN11:AO11,Y11:Z11,F11:V11,K13:AK13,Q14:AK14,J15:T15,P38:P39").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    With ws.Range("AV43").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Next ws

Err:
Application.EnableEvents = True
End Sub

As you described in the comments you are experiencing some unusal printing behavior. You can use this less elegant version of the code above, this should circumvent your problem:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
On Error GoTo Err
Application.EnableEvents = False

Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets

    ' change color to white
    ws.Range("A6:BD53").Interior.ColorIndex = 0

Next ws

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

For Each ws In ActiveWindow.SelectedSheets

    ' AfterPrint_ change color back
    With ws.Range("G6:BD6,G8:AD8,AI8:BD8,AN11:AO11,Y11:Z11,F11:V11,K13:AK13,Q14:AK14,J15:T15,P38:P39").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    With ws.Range("AV43").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Next ws

Err:
Application.EnableEvents = True
End Sub
user_91733790
Related