One macro prevents another from working

**KevinThomas** · 05-07-2009, 03:16 AM

Hi all,
I have a macro built in to one of my worksheets (under view code).

This prompts a user to select either “Ok” or “Cancel” from a message box.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set b = Range("B:B")


If Intersect(t, b) Is Nothing Then Exit Sub
Application.EnableEvents = False
x = MsgBox("RESET CALCULATIONS??'", vbOKCancel, "Reset?")
If x = vbCancel Then Exit Sub

ActiveSheet.Unprotect 

ActiveCell.Offset(-1, 11).Select


Selection.FormulaR1C1 = _
        "=IF(AVERAGE(Table!R3C4:R20C4)>5,IF(AVERAGE(Table!R3C4:R20C4)<31,IF(INDIRECT(""b""&ROW())=0,"""",IF(INDIRECT(""q""&ROW())=""X"",IF(INDIRECT(""y""&ROW())=1,INDIRECT(""u""&ROW()),""""),"""")),""""),"""")"
ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowFiltering:=True, 
End Sub

I also have a macro that is designed to run upon save or closure of the workbook which is built in to “this workbook”.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     


ThisWorkbook.Activate
ThisWorkbook.Sheets("Main").Select
Range("A5:O310").Select
    Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Range("B5").Select
End Sub

No for some reason, if “cancel” is selected from the first macro – it prevents the second macro from running on closure. If “ok” (or the first macro is not run) the second one will run perfectly.

Can anyone help please??

**JBeaucaire** · 05-07-2009, 03:19 AM

You have to turn ENABLEEVENTS back on at the bottom of the Worksheet_Change macro:

Application.EnableEvents = False

your code....

Application.EnableEvents = True

This piece of code MEANS "turn off other macros for now"...so you have to turn them back on when you're done with your code.

**JBeaucaire** · 05-07-2009, 03:27 AM

In fact, I would reorder your macro events to this order, it appears you could accidentally abort your code with the MSGBOX and still leave the events turned off:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    
        If MsgBox("RESET CALCULATIONS??'", vbYesNo, "Reset?") = vbNo Then Exit Sub
    
        Application.EnableEvents = False
    
        ActiveSheet.Unprotect
        ActiveCell.Offset(-1, 11).FormulaR1C1 = _
                "=IF(AVERAGE(Table!R3C4:R20C4)>5,IF(AVERAGE(Table!R3C4:R20C4)<31,IF(INDIRECT(""b""&ROW())=0,"""",IF(INDIRECT(""q""&ROW())=""X"",IF(INDIRECT(""y""&ROW())=1,INDIRECT(""u""&ROW()),""""),"""")),""""),"""")"
        ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowFiltering:=True
        
        Application.EnableEvents = True
    End If
End Sub

**KevinThomas** · 05-07-2009, 04:32 AM

Absolute star - thank you very much

**JBeaucaire** · 05-07-2009, 05:32 AM

My pleasure! If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED].

(Also, use the "scales" icon across from the post dates to leave Feedback, it is appreciated)