Excel Guru "Force Enable Macro" solution seriously flawed.

Some more updates as feedback comes through from my coworkers:

1. If you try you choose SaveAs without first choosing the type from the ribbon, you get the same 1004 Error.

2. Once again, this error is followed by all events being disabled within the application until you restart Excel entirely (as can be seen by the code).

Quick answer to one of your problems.

2. Once again, this error is followed by all events being disabled within the application until you restart Excel entirely (as can be seen by the code).

You should write yourself a short VBA routine to run when things go awry. Store it in a separate workbook from any projets under development.

 

Sub ResetExcelEnvironment()

  With Application.

    .EnableEvents = True

    .ScreenUpdating = True

    .Calculation = xlCalculationAutomatic

  End With

End Sub

---

Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
Peltier Tech Blog

Another piece of your puzzle:

If SaveAsUI = True Then
is testing if the File --> SaveAs processes were called.  This would not be True if you just used File --> Save or the Save icon.

Because you've interrupted the normal SaveAs processing then the code is manually going through the rest of the SaveAs process by bringing up the GetSaveAsFilename() dialog.  If no file is selected/name provided, it will return False, otherwise it returns the path/filename to use as the SaveAs name.

It would appear (not tested) that this section of code:
   With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
should give you your event driven macros the ability to execute again.  But hitting the 1004 error earlier on is forcing you to terminate the process before the code gets to that point.

---

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

Another piece of your puzzle:

If SaveAsUI = True Then
is testing if the File --> SaveAs processes were called.  This would not be True if you just used File --> Save or the Save icon.

Because you've interrupted the normal SaveAs processing then the code is manually going through the rest of the SaveAs process by bringing up the GetSaveAsFilename() dialog.  If no file is selected/name provided, it will return False, otherwise it returns the path/filename to use as the SaveAs name.

It would appear (not tested) that this section of code:
   With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
should give you your event driven macros the ability to execute again.  But hitting the 1004 error earlier on is forcing you to terminate the process before the code gets to that point.

---

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

Thanks for the response.

I do understand why events are being disabled, but I don't understand how to get around the 1004 error.  I'm perfectly capable of writing an On Error GoTo statement to enable events in the event of an error, but I have to be able to write code that let's the user save the file any which way they please. 

It appears to me that this statement:

    If SaveAsUI = True Then
        vFilename = Application.GetSaveAsFilename( _
                    fileFilter:="Excel Files (*.xls*), *.xls*")
        If CStr(vFilename) = "False" Then
            bSaved = False
        Else
            Call HideAllSheets
            ThisWorkbook.SaveAs vFilename
            Application.RecentFiles.Add vFilename
            Call ShowAllSheets
            bSaved = True
        End If
    Else
        Call HideAllSheets
        ThisWorkbook.Save
        Call ShowAllSheets
        bSaved = True
    End If

Should allow the user to save without using the SaveAs UI, but it clearly only works if the File-Save option is chosen.  I don't understand why it fails when the SaveAlert is used to save the file, nor why it fails when a file is overwritten.  Any advice on how to get around this is very much appreciated.

 

Thanks,

Fred

Just checked:

Though I edited the code very slightly, the original solution, copied and pasted into a test workbook and completely unchanged suffers from the same exact problems. 

In another thread, Mike. H claimed this was a semi-robust workaround, but I'm going to have to completely disagree.  This solution is the opposite of robust. The following ways are how a User can get save/close an Excel file:

1. File-SaveAs, new name

2. File-SaveAs, old name (i.e. overwrite)

3. File-Save

4. Close-Save

 

Both 2 and 4 result in errors.  50% of the ways a user can save/close this file result in errors.

If anyone can help me write code that makes this a more robust solution, I'd greatly appreciate it.

 

More updates.

I was able to add the before_close event code:

    Dim answer As VbMsgBoxResult
    If Application.Calculation = xlCalculationAutomatic Then
        If ThisWorkbook.Saved = False Then
            Application.Calculation = xlCalculationManual
            answer = MsgBox("Do you want to save the changes you made to " & ThisWorkbook.Name & "?", vbYesNoCancel)
            If answer = vbYes Then
                Call HideAllSheets
                ThisWorkbook.Close True
            ElseIf answer = vbNo Then
                ThisWorkbook.Close False
            ElseIf answer = vbCancel Then
                Application.Calculation = xlCalculationAutomatic
                Cancel = True
            End If
        End If
    End If

To get the fourth option to work.  This leaves the following two errors I still come across:

1. If I try to overwrite a file, I get a 1004 error.

2. If I try to use SaveAs, and change the name of the file, without first specifying which file type through the office button, the file doesn't actually save. 

 

I'd really appreciate some help on this one.  I'm supposed to have it figured out by this afternoon.

 

Thanks,

Fred

When saving in 2007, you have to take care when specifying the file type to save the file as. GetSaveAsFilename needs a modified filter, and you need to specify the filetype in the SaveAs command.

---

Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
Peltier Tech Blog

Nuculerman, this is a great thread.  You are right on target regarding the problems with the Excel Guru solution.  It is NOT a workable solution.  Below is my code.  It resolves all of the problems that you have mentioned with the Guru code.  My code is somewhat similar to yours above but with some improvements.  I believe that it is the most complete solution yet posted.  I had to cut out some extraneous statements, so I haven't tested this exact version.  If you find errors, let me know.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ChAction As Variant

If ThisWorkbook.Saved = False Then
    ChAction = MsgBox("Do you want to save the changes you made to '" & _
      ThisWorkbook.Name & "'?", vbYesNoCancel + vbExclamation)
    Select Case ChAction
    Case vbCancel
        Cancel = True
    Case vbYes
        'Perform the save (not save-as)
        If MySave(False) = "Saved" Then
            'Close the workbook without saving the changes to prevent Excel from
            'asking about saving before closing
            ThisWorkbook.Close False
        Else
            Cancel = True
        End If
    Case vbNo
        'To prevent Excel from asking about saving before closing
        ThisWorkbook.Saved = True
    End Select

End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
Dim wsActive As Worksheet
Set wsActive = ActiveSheet 'Record active worksheet

Call MySave(SaveAsUI) 'perform the save or save-as using code

Call ShowAllSheets

wsActive.Activate 'Restore file to where user was
Cancel = True 'cancel the user-interface save or save-as
End Sub

Function MySave(Optional SaveAsUI As Boolean) As String
'Use code to perform the save or save-as
'Returns:  Saved, Not Saved
Dim strName As String
Dim intDoSave As Integer
Dim bSaved As Boolean
Dim bSaveAsHere As Boolean
MySave = "Not Saved"

'Turn off screen flashing and stop Excel events from occurring
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

'Perform the save-as or save
If SaveAsUI = True Then 'Perform save-as
    'Display the save-as dialog box without defaulting to original path
    strName = Application.GetSaveAsFilename
    If strName = "False" Then 'user cancelled--do nothing
        bSaved = False
    Else 'save the workbook using the supplied filename
        Call HideAllSheets
        'If the save-as path\name is the same as the current workbook, we can do a regular save
        If UCase(strName) = UCase(ThisWorkbook.FullName) Then
            ThisWorkbook.Save
            bSaved = True
        Else 'do a save-as
            If Dir(strName) <> "" Then 'if the file exists, display warning

                intDoSave = MsgBox("This file already exists.  Do you want to replace it?", vbYesNoCancel + vbExclamation)

                If intDoSave = vbYes Then
                    'So you don't get the "already exists" prompt which causes an error if user chooses No or Cancel
                    Kill (strName)
                Else 'no or cancel
                    GoTo exitSection
                End If
            End If
            ThisWorkbook.SaveAs strName
            Application.RecentFiles.Add strName
            bSaved = True
        End If
    End If
Else 'Perform regular save
    Call HideAllSheets
    ThisWorkbook.Save
    bSaved = True
End If

'Restore screen updates and events
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

'If the workbook got saved
If bSaved Then
    ThisWorkbook.Saved = True
    MySave = "Saved"
End If

End Function

Private Sub Workbook_Open()

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False
    Call ShowAllSheets
    Application.ScreenUpdating = True
    ThisWorkbook.Saved = True

End Sub

Private Sub HideAllSheets()

    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()

    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

This isn't quite working for me. 

This isn't quite working for me. 

When I try to compile it I get an error: "label not defined"

at GoTo exitSection

in the Mysave function

If you change GoTo exitSection to Exit Function it works perfectly.

You also need to include:

Option Explicit

Const WelcomePage = "Macros"

At the top of the code.  The corrected version is pasted below.

 

Option Explicit
' this code is from http://answers.microsoft.com/en-us/office/forum/office_2007-customize/excel-guru-force-enable-macro-solution-seriously/cdf9885f-f4e3-4424-8fab-7adc541d1c6f
Const WelcomePage = "Macros"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ChAction As Variant

If ThisWorkbook.Saved = False Then
    ChAction = MsgBox("Do you want to save the changes you made to '" & _
      ThisWorkbook.Name & "'?", vbYesNoCancel + vbExclamation)
    Select Case ChAction
    Case vbCancel
        Cancel = True
    Case vbYes
        'Perform the save (not save-as)
        If MySave(False) = "Saved" Then
            'Close the workbook without saving the changes to prevent Excel from
            'asking about saving before closing
            ThisWorkbook.Close False
        Else
            Cancel = True
        End If
    Case vbNo
        'To prevent Excel from asking about saving before closing
        ThisWorkbook.Saved = True
    End Select

End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
Dim wsActive As Worksheet
Set wsActive = ActiveSheet 'Record active worksheet

Call MySave(SaveAsUI) 'perform the save or save-as using code

Call ShowAllSheets

wsActive.Activate 'Restore file to where user was
Cancel = True 'cancel the user-interface save or save-as
End Sub

Function MySave(Optional SaveAsUI As Boolean) As String
'Use code to perform the save or save-as
'Returns:  Saved, Not Saved
Dim strName As String
Dim intDoSave As Integer
Dim bSaved As Boolean
Dim bSaveAsHere As Boolean
MySave = "Not Saved"

'Turn off screen flashing and stop Excel events from occurring
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

'Perform the save-as or save
If SaveAsUI = True Then 'Perform save-as
    'Display the save-as dialog box without defaulting to original path
    strName = Application.GetSaveAsFilename
    If strName = "False" Then 'user cancelled--do nothing
        bSaved = False
    Else 'save the workbook using the supplied filename
        Call HideAllSheets
        'If the save-as path\name is the same as the current workbook, we can do a regular save
        If UCase(strName) = UCase(ThisWorkbook.FullName) Then
            ThisWorkbook.Save
            bSaved = True
        Else 'do a save-as
            If Dir(strName) <> "" Then 'if the file exists, display warning

                intDoSave = MsgBox("This file already exists.  Do you want to replace it?", vbYesNoCancel + vbExclamation)

                If intDoSave = vbYes Then
                    'So you don't get the "already exists" prompt which causes an error if user chooses No or Cancel
                    Kill (strName)
                Else 'no or cancel
                    Exit Function
                End If
            End If
            ThisWorkbook.SaveAs strName
            Application.RecentFiles.Add strName
            bSaved = True
        End If
    End If
Else 'Perform regular save
    Call HideAllSheets
    ThisWorkbook.Save
    bSaved = True
End If

'Restore screen updates and events
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

'If the workbook got saved
If bSaved Then
    ThisWorkbook.Saved = True
    MySave = "Saved"
End If

End Function

Private Sub Workbook_Open()

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False
    Call ShowAllSheets
    Application.ScreenUpdating = True
    ThisWorkbook.Saved = True

End Sub

Private Sub HideAllSheets()

    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()

    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