PDA

View Full Version : Why is Excel asking me to save......twice?



sconly
10-28-2011, 02:38 AM
I've got the following code in my workbook and excel asks me (twice) if i want to save changes, once beforre the SaveAs dialog box appears and again afetr a click Save on the SaveAs dialog.

Is there something wrong with my code?

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

Application.DisplayAlerts = False

If SaveAsUI = True Then
Cancel = True
Dim FileSaveAsName As Variant
FileSaveAsName = Application.GetSaveAsFilename("", "Excel 2010 Macro Workbook (*.xlsm),*.xlsm", 52)
If FileSaveAsName = False Then
Cancel = True
Exit Sub
Else
ActiveWorkbook.SaveAs FileSaveAsName, 52
Exit Sub
End If
'Else
' Cancel = True
' ActiveWorkbook.Save
' Exit Sub
End If

Application.DisplayAlerts = True

End Sub

I've stepped through the code and it runs down to the red text line and then jumps back to the top of the procedure.

Thanks.

GTO
10-28-2011, 04:17 AM
Hi there,

It looks to me that you want to limit the SaveAs to .xlsm format. At home (Excel2000), so no ability to exactly replicate, but I think you just want to prohibit effective recursion. Try:
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FileSaveAsName As Variant
Static bolInProcess As Boolean

'// Use a flag to prevent effective recurse //
If Not bolInProcess Then
If SaveAsUI = True Then
'// If we get here, then kill alerts //
Application.DisplayAlerts = False
bolInProcess = True
Cancel = True
FileSaveAsName = Application.GetSaveAsFilename("", "Excel 97-2003 Workbook (*.xls),*.xls")
If FileSaveAsName = False Then
MsgBox "You cancelled"
Else
ActiveWorkbook.SaveAs FileSaveAsName, &HFFFFEFD1
End If
Application.DisplayAlerts = True
End If
bolInProcess = False
End If
End Sub

AirCooledNut
08-22-2012, 03:59 PM
Nice post and very helpful. Using this post and the one ozgrid.com I came up with this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'This will handle the saving of the workbook to insure the appropriate sheets are hidden when being saved. This tries to keep the macro-enabled
'security enforced upon the workbook.
Dim FileSaveAsName As Variant, bFlag As Boolean
Static bInProcess As Boolean 'Keep the variable's value as long as the code is running...which is what'll happen during the recursion of this event.

If Not bInProcess Then 'If the flag is FALSE or empty then go through process to save file
Application.EnableEvents = False 'Don't want any other events getting fired off...
Application.DisplayAlerts = False '...nor any "Are you sure?" warnings displayed.
If SaveAsUI = True Then 'Save As... was selected
bInProcess = True 'The .SaveAs will cause this routine to execute so this STATIC variable will insure we don't infinitely recurse
Cancel = True
FileSaveAsName = Application.GetSaveAsFilename(ThisWorkbook.Name, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
If FileSaveAsName = False Then
MsgBox "Save was cancelled.", vbOKOnly 'User decided to Cancel the save
Else
bFlag = False
If ThisWorkbook.Worksheets(CleanRefersTo(ThisWorkbook.Names("Year_Current").RefersTo)).Visible = True Then
'If the current year sheet is visible then hide the sheets as necessary
SaveStateAndHideSheets
bFlag = True
End If
ThisWorkbook.SaveAs FileSaveAsName, xlOpenXMLWorkbookMacroEnabled 'Saves the file as a macro-enabled Excel workbook
If bFlag Then UnHideSheets 'Show all the appropriate sheets if they had been hidden by above
End If
Else 'Normal Save or workbook close/exit was selected
bInProcess = True 'The actual .Save will cause this routine to execute so this STATIC variable will insure we don't infinitely recurse
Cancel = True
bFlag = False
If ThisWorkbook.Worksheets(CleanRefersTo(ThisWorkbook.Names("Year_Current").RefersTo)).Visible = True Then
'If the current year sheet is visible then hide the sheets as necessary
SaveStateAndHideSheets
bFlag = True
End If
ThisWorkbook.Save 'Saves the file as a macro-enabled Excel workbook
If bFlag Then UnHideSheets 'Show all the appropriate sheets if they had been hidden by above
End If
bInProcess = False 'Just to be on the safe side, set variable to default value
Application.EnableEvents = True
Application.DisplayAlerts = True
'These next two lines allow a File >> Save and then File >> Close without the application asking to save the file
Cancel = True
ThisWorkbook.Saved = True
End If
End Sub
I'm not an Excel guru so if you can see improvements please add to the discussion. The down side to this is that if you select File >> Close, the pop-up dialog will repeat forever if you keep clicking the Save button. Clicking the Cancel or Don't Save will allow it to exit accordingly. Performing a File >> Exit will work as expected as well. Any way to keep the repeating of the Save from happening would be appreciated.

Excel 2010 in Windows 7.

GTO
08-22-2012, 06:52 PM
Greetings Toby,

Welcome to vbaexpress:hi:

Could you include 'CleanRefersTo()' and 'SaveStateAndHideSheets()'? It might be best just to attach a workbook with the save code as you now have it.

Mark

snb
08-23-2012, 12:47 AM
It looks as if you want to build what's inbuilt in Excel itself.

I think this might suffice:



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
if saved then
application.enableevents=false
ActiveWorkbook.SaveAs FileSaveAsName, 52
application.enableevents=true
End If
Application.DisplayAlerts = True
End Sub

AirCooledNut
08-23-2012, 09:19 AM
Greetings Toby,

Welcome to vbaexpress:hi:

Could you include 'CleanRefersTo()' and 'SaveStateAndHideSheets()'? It might be best just to attach a workbook with the save code as you now have it.

Mark Function CleanRefersTo(sValue As String) As String
'Cleans up the RefersTo string (remove quotes and equal sign, if any).

sValue = Replace(sValue, """", "") 'Remove quotes
sValue = Replace(sValue, "=", "") 'Remove equal sign
CleanRefersTo = sValue
End Function
The SaveStateAndHide() and the UnHideSheets() routines come from Chip Pearson's page: w w w . cpearson . com/excel/EnableMacros.aspx so credit goes to Chip for that.

AirCooledNut
08-23-2012, 09:35 AM
It looks as if you want to build what's inbuilt in Excel itself.
... In a way, yes. The user's workbooks have protection and special right-click menu items that need to be enforced otherwise they can mess up formulas and break another application that feeds of their results. By using Chip's method [of hiding the sheets if macros are not allowed] we are able to enforce users to turn on macros and they can work without much fear of breaking financial calculations and 'stuff'. As you can see, if the user wants to save the workbook elsewhere and as something else it forces it to be saved as an openXML, macro-enabled file. Yes, yes, there may be a user who doesn't follow the rules and is able to circumvent some of this but at least it will be obvious and their workbook can be rejected (i.e., they get to redo all their work, punishment enough!) by the finance team. 99%, if not all, of the users aren't looking to break the application and are wanting these controls to make their job easier :cloud9:

Note: I tried to attach the workbook but there's a technical error and an email has been automatically sent. So no workbook to view just yet.

GTO
08-24-2012, 12:15 AM
Nice post and very helpful...

Hi Toby,

I should have said thank you for that comment. Thank you very much:cloud9:


...The down side to this is that if you select File >> Close, the pop-up dialog will repeat forever if you keep clicking the Save button. Clicking the Cancel or Don't Save will allow it to exit accordingly. Performing a File >> Exit will work as expected as well. Any way to keep the repeating of the Save from happening would be appreciated.

Maybe I am missing something, but I had a quick go at it and believe I found a glitch. Chip Pearson writes excellent code, so that may just mean that I missed something from his page at: http://www.cpearson.com/excel/EnableMacros.aspx , but I did read through it several times.

At least as I can see, there is a hiccup in that SaveStateAndHide() is called in Chip's example code by the Workbook_BeforeClose event. However, while SaveStateAndHide() changes sheets' visibility and several other workbook properties, it doesn't save the workbook. Thus (after attempting to close the workbook), Excel's "Do you want to save?" message pops up. If you select Yes, we run through the BeforeSave code, which again saves the workbook, but then jump right back into the BeforeClose event (which of course again changes workbook properties, forcing Excel to ask the now eternal question, "Would you like to save changes?"

I did not get to test well, but as you are using the BeforeSave event (which, in my humble opinion is better), I think we can just ditch calling SaveStateAndHide in BeforeClose.

At least as I read it, the code/procedures called in BeforeSave are ensuring that the workbook has only the "Introduction", or what I refer to as the "Macro Warn" sheet displayed while the workbook is in a true Saved state.

Hope that's at least a good step,

Mark

AirCooledNut
08-24-2012, 08:46 AM
Yes, I found the BeforeClose event troublesome so that's why I moved it to the BeforeSave. Here's my BeforeClose event:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Remove all custom menu items before closing the workbook!
Call RemoveShortcutMenu
End Sub

Another important event:
Private Sub Workbook_Open()

''''''''''''''''''''''''''''''''''''''''''''''''
' Unhide the sheets. This Workbook_Open event
' will run only if macros are enabled. If macros
' are not enabled, this code will not run and
' only the introduction sheet will be visible.
''''''''''''''''''''''''''''''''''''''''''''''''
If CheckVBaccess() Then
UnHideSheets
Else
Call MsgBox("This application requires the use of macros and the current security settings are too strict." & vbCrLf _
& vbCrLf & "Please select File >> Options, Trust Center >> Trust Center Settings... then select Macro Settings and insure the box under" _
& vbCrLf & """Developer Macro Settings"" is checked." _
& vbCrLf & "" _
& vbCrLf & "Clicking the OK button will Exit this application..." _
, vbExclamation, "Trust Center -- Macro Setting change required.")
ThisWorkbook.Saved = True
Application.EnableEvents = False
Application.Quit
End If
End Sub

Private Function CheckVBaccess() As Boolean
'Returns TRUE if access to the VB project is allowed.
Dim vbProject As Variant

On Error Resume Next
vbProject = Application.VBE.ActiveVBProject.Name
CheckVBaccess = (Err.Number = 0)
End Function So you can see where I modified how I use Chip's code (BTW Chip,:bow:). I like Chip's method as I have visible and xlVeryHidden sheets (mostly they hold pivot and list table data), plus the users can add their own sheets and hide sheets as well, so it works very well.

I trimmed down the file and have attached it for your perusal. I just found an issue with the SaveAs part of the routine so if you hit that error just skip to the next line in the code and F5 it:whip