Before_Print - Single Step Dialog Box

[jim may]

When I try and Step through this code I get a Single Step Dialog Box with several buttons on it, yet Excel IS FROZEN UP on me -- I must go to the
Task Manager and Close ALL my Excel Files...
What is Happening?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'If the Master Sheet is Printed
Application.EnableEvents = False

If ActiveSheet.Name = "Master" Then
SLr = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
    ActiveSheet.PageSetup.PrintArea = "$A$5:$H$" & SLr & ""
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        '.FitToPagesTall = 0
        .RightFooter = Now
    End With
ElseIf ActiveSheet.Name = "Initial-Import" Then
SLr = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
    ActiveSheet.PageSetup.PrintArea = "$A$5:$U$" & SLr & ""
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        '.FitToPagesTall = 2
        .RightFooter = Now
    End With
ElseIf ActiveSheet.Name = "FinalReport" Then
If Not MasterDirty Then
SLr = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
    ActiveSheet.PageSetup.PrintArea = "$A$5:$O$" & SLr & ""
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        '.FitToPagesTall = 2
        .RightFooter = Now
    End With
Else
MsgBox "There have been Changes to the Master Sheet" & vbNewLine & _
    "Which have Not been transferred to your Final" & vbNewLine & _
    "Report.  Return to the Master Sheet and Click" & vbNewLine & _
    "the Update Button BEFORE Proceeding."
Cancel = True
End If
End If
Application.EnableEvents = True
End Sub

 

[JackDanIce]

I think it's because of this:

Application.EnableEvents = False

"This code processes the cell change event at the Sheet level, but the line Application.EnabledEvents = False prevents the Worksheet and Applicaton SheetChange events from being raised. Indeed, this line of code suppresses all events from being raised until its value is reset to True. Note that Excel never automatically sets Application.EnabledEvents back to True (as it does do with the ScreenUpdating property). It is up to your code, including well designed error handling code, to ensure that Application.EnabledEvents is properly reset to True. See Error Handling In VBA for more information about error handling code."

http://www.cpearson.com/excel/Events.aspx
<!-- ======================== --><!-- Sample Event Procedure--><!- ======================== -->

 

[MrKowz]

Jim,

What is MasterDirty? (Highlighted below) If it is a separate procedute, please post the code for that as well.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'If the Master Sheet is Printed
Application.EnableEvents = False
If ActiveSheet.Name = "Master" Then
    SLr = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
        ActiveSheet.PageSetup.PrintArea = "$A$5:$H$" & SLr & ""
        With ActiveSheet.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
            '.FitToPagesTall = 0
            .RightFooter = Now
        End With
ElseIf ActiveSheet.Name = "Initial-Import" Then
    SLr = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
        ActiveSheet.PageSetup.PrintArea = "$A$5:$U$" & SLr & ""
        With ActiveSheet.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
            '.FitToPagesTall = 2
            .RightFooter = Now
        End With
ElseIf ActiveSheet.Name = "FinalReport" Then
    If Not [B][COLOR=red]MasterDirty[/COLOR][/B] Then
    SLr = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
        ActiveSheet.PageSetup.PrintArea = "$A$5:$O$" & SLr & ""
        With ActiveSheet.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
            '.FitToPagesTall = 2
            .RightFooter = Now
        End With
    Else
    MsgBox "There have been Changes to the Master Sheet" & vbNewLine & _
        "Which have Not been transferred to your Final" & vbNewLine & _
        "Report.  Return to the Master Sheet and Click" & vbNewLine & _
        "the Update Button BEFORE Proceeding."
    Cancel = True
    End If
End If
Application.EnableEvents = True
End Sub

 

[jim may]

In my WB_Open Procedure I have the line:

MasterDirty = False

In my "master" sheet Change Event code I have:

Private Sub Worksheet_Change(ByVal Target As Range)
MasterDirty = True
ActiveSheet.Shapes("Button 1").Visible = True
End Sub

So MasterDirty warns me if There have been ant changes to my Master Sheet..

 

[jim may]

Additionally,

This line:
Public MasterDirty As Boolean

is the 1st line in the Declarations section of my Only Standard Module