EnableEvents = True does not work

[RomulusMilea]

Hello all,

I use Excel 2007. I have got the entire code below, meant to force the user to open the file with macros enabled (we prefer to use this option) and also meant to ask the user for number of appraisers, before doing anything to the file.

I have put Application.EnableEvents back to True (see the line below, marked in bold red), but if the user presses Cancel button "during" procedure "Set_no_of_appraisers" (see below), the EnableEvents stays on False.

This is confirmed also by Excel Immediate Window, it says "False" .

I have tried to solve this by using others experience all over the Internet, but no thread/topic/forum explains this simply and clearly to a VBA novice as I am .

When is the most appropriate to set EnableEvents to True ?

Where am I going wrong ?

Please provide an answer, based on the code below. Thank you so much.

Option Explicit
Const WelcomePage = "Enable macros"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False
'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the BLOODY changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
Call CustomSave
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If
'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close SaveChanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False
'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True
'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub
Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
Set_no_of_appraisers
End Sub
Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False
'Record active worksheet
Set aWs = ActiveSheet
'Hide all sheets
Call HideAllSheets
'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
'On Error Resume Next
End If
'Restore file to where user was
Call ShowAllSheets
aWs.Activate
'Restore screen updates
Application.ScreenUpdating = True
End Sub
Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet
Worksheets(WelcomePage).Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws
Worksheets(WelcomePage).Activate
End Sub
Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub
 
Private Sub Set_no_of_appraisers()
'Created by Romulus Milea, Romulus.Milea@GMail.com;
'Version 1.0, latest update: 1/Sep/2011;
'Code history:
'Version 1.0  1/Sep/2011 First issue
Application.EnableEvents = False
Dim InputBoxString As String
Dim ValidEntry As Boolean
Dim UserEntry As Variant
Worksheets("Gage R&R Continuous (Rows)").Range("B14").ClearContents
InputBoxString = "Please enter number of APPRAISERS (2 or 3):"
ValidEntry = False
Do
    UserEntry = InputBox(InputBoxString)
    If IsNumeric(UserEntry) Then
        If (UserEntry = 2) Or (UserEntry = 3) Then
            ValidEntry = True
        Else
            InputBoxString = "Your previous numeric entry was INVALID."
            InputBoxString = InputBoxString & vbCrLf & vbCrLf
            InputBoxString = InputBoxString & "You must enter 2, or 3. Please re-try !"
        End If
    Else
        InputBoxString = "Your previous entry was NOT numeric, therefore is it INVALID."
        InputBoxString = InputBoxString & vbCrLf & vbCrLf
        InputBoxString = InputBoxString & "You must enter 2, or 3. Please re-try !"
    End If
    If UserEntry = "" Then
            MsgBox "You entered no value, or you cancelled the entry, therefore active file will be now closed, without being saved."
            ActiveWorkbook.Close SaveChanges:=False
            Application.EnableEvents = True
    End If
Loop Until ValidEntry = True
 
Worksheets("Gage R&R Continuous (Rows)").Range("B14").Value = Val(UserEntry)
Cells(1, 1).Select
MsgBox "Number of appraisers is now set to " & UserEntry & " (see cell B15). " & _
"If you need to change this value you must close the file and open it again."
End Sub

 

[Weaver]

Personally, I'd include it at every point you're exiting the subroutine, just to be safe.

 

[Weaver]

Duplicate post

 

[RomulusMilea]

Hello Weaver,

Thank you for your suggestions, I will try them.

If this is a duplicate post, which is the original one ? Thank you.

 

[Weaver]

Hello Weaver,

Thank you for your suggestions, I will try them.

If this is a duplicate post, which is the original one ? Thank you.

Sorry, I meant I posted a duplicate response.

 

[RomulusMilea]

Dear forum moderators,

Please help me to have your advise here, the answer from Weaver did not entirely solve the problem, there are events in the code that would be blocked by EnableEvents = False, the solution should refer to entire code, seen as a whole.

Thank you once again.

 

[mikerickson]

"include it at every point you're exiting the subroutine, just to be safe."
The advise is sound, the difficulty is that there are many points where the subroutine can be exited and it can be difficult to trace them all.

One issue that I see is that in the cancel situation, the Do Loop continues. Perhaps

Dim validEntry As Boolean

Do
    UserEntry = InputBox(InputBoxString)
    
    validEntry = Val(UserEntry) = 2 Or Val(UserEntry) = 3
    
    InputBoxString = "Your previous numeric entry was INVALID."
    InputBoxString = InputBoxString & vbCrLf & vbCrLf
    InputBoxString = InputBoxString & "You must enter 2, or 3. Please re-try !"
        
    If UserEntry = "" Then
            MsgBox "You entered no value, or you cancelled the entry, therefore active file will be now closed, without being saved."
            ActiveWorkbook.Close SaveChanges:=False
            validEntry = True
    End If
Loop Until validEntry

Application.EnableEvents = True

 

[RomulusMilea]

Thank you very much Sir, I will try your suggestion. Have a great day.

 

[Weaver]

Dear forum moderators,

Please help me to have your advise here, the answer from Weaver did not entirely solve the problem, there are events in the code that would be blocked by EnableEvents = False, the solution should refer to entire code, seen as a whole.

Thank you once again.

Why would you make a plea to the forum moderators over this? As mikericson suggested, it's a sound suggestion

All you have to do is insert a new line directly before each reference to "exit sub" or "end sub" and set enableEvents back to true. How would that interfer with other code?

 

[mikerickson]

One of the principles of structured programming is "one way in, one way out".
Loosely, that means that no matter what happens, the line End Sub is executed. That means no Exit Sub's.

In this case it might look like

    Do
        UserEntry = InputBox(InputBoxString)
        InputBoxString = "Please enter only 2 or 3" & vbCr & vbCr & "Please enter number of APPRAISERS (2 or 3):"
    Loop Until (UserEntry <> vbNullString) Imp (UserEntry = "2" Or UserEntry = "3")
 
    If UserEntry = vbNullString Then
        MsgBox "You entered no value, or you cancelled the entry, therefore active file will be now closed, without being saved."
        ActiveWorkbook.Close SaveChanges:=False
    Else
        Worksheets("Gage R&R Continuous (Rows)").Range("B14").Value = Val(UserEntry)
        Cells(1, 1).Select
        MsgBox "Number of appraisers is now set to " & UserEntry & " (see cell B15). " & _
            "If you need to change this value you must close the file and open it again."
    End If
    
End Sub

One advantage of this approach is that all clean-up statements can be put immediately before the End Sub and one doesn't need to trace all of the what-if-this-what-if-that-and-the-other possibilities. So your routine would become.

Private Sub Set_no_of_appraisers()
    Dim InputBoxString As String
    Dim UserEntry As Variant
    Application.EnableEvents = False
    
    Worksheets("Gage R&R Continuous (Rows)").Range("B14").ClearContents
    InputBoxString = "Please enter number of APPRAISERS (2 or 3):"

    Do
        UserEntry = InputBox(InputBoxString)
        InputBoxString = "Please enter only 2 or 3" & vbCr & vbCr & "Please enter number of APPRAISERS (2 or 3):"
    Loop Until (UserEntry <> vbNullString) Imp (UserEntry = "2" Or UserEntry = "3")
 
    If UserEntry = vbNullString Then
        MsgBox "You entered no value, or you cancelled the entry, therefore active file will be now closed, without being saved."
        ActiveWorkbook.Close SaveChanges:=False
    Else
        Worksheets("Gage R&R Continuous (Rows)").Range("B14").Value = Val(UserEntry)
        Cells(1, 1).Select
        MsgBox "Number of appraisers is now set to " & UserEntry & " (see cell B15). " & _
            "If you need to change this value you must close the file and open it again."
    End If
    
    Application.EnableEvents = True
End Sub

By the way, a pure test for the Cancel button (as opposed to entering "") would be StrPtr(UserEntry)=0.