Apply VBA Code When User Has Multiple Sheets Active

[Bryan Sonnier]

The issue i'm have is i'm trying to unprotect two pages uncolor cells on two pages print those pages and then recolor and reprotect.
Sometimes the User will print one page sometimes two. When printing 2 pages they have to select the 2 pages with control + click which makes both pages active and then hit print. When my code runs with two active pages i get an error. Any workaround would be great. Below is my code. i know it not efficient but it mostly gets the job done. Any suggestions on efficiency would be greatly appreciated. Thank you.

Sub UnColorCells_CoverPage()


Dim Data As Range
Dim cell As Range
Set currentsheet = ActiveWorkbook.Sheets("Cover Page")
Set Data = Intersect(currentsheet.UsedRange, currentsheet.Range("A1:AA100"))

Sheets("Cover Page").Protect
For Each cell In Data
If cell.Interior.Color = RGB(189, 215, 238) Then
cell.Interior.Color = RGB(254, 255, 255)
End If
Next

End Sub
Sub ColorCells_CoverPage()


Dim Data As Range
Dim cell As Range
Set currentsheet = ActiveWorkbook.Sheets("Cover Page")
Set Data = Intersect(currentsheet.UsedRange, currentsheet.Range("A1:AA100"))

For Each cell In Data
If cell.Interior.Color = RGB(254, 255, 255) Then
cell.Interior.Color = RGB(189, 215, 238)
End If
Next
Sheets("Cover Page").Unprotect

End Sub
Sub UnColorCells_Quote()


Dim Data As Range
Dim cell As Range
Set currentsheet = ActiveWorkbook.Sheets("Quote")
Set Data = Intersect(currentsheet.UsedRange, currentsheet.Range("A1:AA100"))


For Each cell In Data
If cell.Interior.Color = RGB(189, 215, 238) Then
cell.Interior.Color = RGB(254, 255, 255)
End If
Next

End Sub
Sub ColorCells_Quote()


Dim Data As Range
Dim cell As Range
Set currentsheet = ActiveWorkbook.Sheets("Quote")
Set Data = Intersect(currentsheet.UsedRange, currentsheet.Range("A1:AA100"))

For Each cell In Data
If cell.Interior.Color = RGB(254, 255, 255) Then
cell.Interior.Color = RGB(189, 215, 238)
End If
Next

End Sub


Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Call UnColorCells_CoverPage
Call UnColorCells_Quote
Application.EnableEvents = False
ActiveSheet.PrintOut
Application.EnableEvents = True
Call ColorCells_CoverPage
Call ColorCells_Quote

End Sub

 

[Yongle]

I am away from my PC so cannot test anything for several days but this may help ...

You can have several sheets selected but only ONE is active

Instead of ActiveSheet.PrintOut perhaps ...

    Dim sh As Object
    For Each sh In ActiveWindow.SelectedSheets
        sh.PrintOut
    Next