Problem Saving with the 'Must Enable Macro' workaround

[Guest]

I've been on macros that, among other things, will display only one of the
worksheets (containing a macro must be enabled reminder) in the workbook if
the file is opened without macros enabled. If opened with macros enabled, it
will display all the other sheets in the book and hide the reminder sheet.

Apparently I haven't got something quiet right with in either my BeforeSave
or BeforeClose procedure here. It is saving and performing as intended when
doing a Save and a Save As, but is not actually saving the workbook when
going by way of Close/"Do you want to save" Yes. Here is my code below.
This is starting to get to complicated for my green mind to pinpoint the
bugs. TIA for any clue!!!


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Set to run each time the workbook is saved
'Userform is displayed, requiring user to provide
'percent complete and if action items are open.
'Also runs CloseHideSheets procedure so that only
'"Sheet1" with the Macro Enable reminder is
'is visible the next time the workbook is opened
'if it is opened without macros enabled
ThisWorkbook.Sheets("Analysis").Activate
UserForm1.Show
Dim strSaveAs As String
Cancel = True
If SaveAsUI = True Then
'get a save as filename from the user
strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel
Files(*.xls), *.xls", 1)
If strSaveAs = "False" Then
'user cancelled the save as operation
Exit Sub
End If
End If
On Error GoTo Event_Exit
Application.ScreenUpdating = False
Application.EnableEvents = False
CloseHideSheets
If SaveAsUI = True Then
Me.SaveAs strSaveAs
Else
Me.Save
End If
Application.EnableEvents = True
OpenUnhideSheets
ThisWorkbook.Sheets("Analysis").Activate
Event_Exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Set to run each time the workbook is closed
'It allows the user to go back to the working
'tabs if they choose to cancel the close
On Error GoTo Event_Exit
Application.ScreenUpdating = False
Dim Msg As String
If Me.Saved = False Then
Msg = "Do you want to save the changes you made to "
Msg = Msg & Me.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Call CloseHideSheets
Me.Save
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
Me.Saved = True
Event_Exit:
Application.ScreenUpdating = True
End Sub

 

[Guest]

Hi Tanya,

I don't think you need to have code for the BeforeClose event. By default,
Excel will prompt the user to save changes (if there's any) when closing the
workbook. If the user says Yes, then your BeforeSave code executes.

If in case you are trapping the BeforeClose event because you want to
display your own message, try the case vbYes part this way:

Case vbYes
If ThisWorkbook.Path = "" Then
'execute the BeforeSave code, prompt for filename
Workbook_BeforeSave True, False
Else
'execute the BeforeSave code
Workbook_BeforeSave False, False
End If

that seemed to work, but I'm not really sure why..

 

[Guest]

Hi Vergel,

I had put in this particular BeforeClose procedure, mainly because what I
was finding was that if the user Canceled the save, the sheets they work in
were already hidden and prevented them from easily going back to working in
the file without confusion.

Thanks for your suggestion here. I will try to start deciphering it here
shortly.

 

[Guest]

Hi Tanya,

The only time a user can cancel on the save is when it's a Save As
operation. If you present the choice to them first before doing anything
else, then, you'll be able to gracefully exit out of the BeforeSave event
without hiding any sheet yet. Here's another suggestion. Try re-arranging
your BeforeSave code like this to give the user the opportunity to cancel at
the start.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Set to run each time the workbook is saved
'Userform is displayed, requiring user to provide
'percent complete and if action items are open.
'Also runs CloseHideSheets procedure so that only
'"Sheet1" with the Macro Enable reminder is
'is visible the next time the workbook is opened
'if it is opened without macros enabled
Dim strSaveAs As String
Cancel = True
If SaveAsUI = True Then
'get a save as filename from the user
strSaveAs = Application.GetSaveAsFilename(Me.Name, "Excel
Files(*.xls), *.xls", 1)
If strSaveAs = "False" Then
'user cancelled the save as operation
Exit Sub
End If
End If

ThisWorkbook.Sheets("Analysis").Activate
UserForm1.Show

On Error GoTo Event_Exit
Application.ScreenUpdating = False
Application.EnableEvents = False
CloseHideSheets
If SaveAsUI = True Then
Me.SaveAs strSaveAs
Else
Me.Save
End If
Application.EnableEvents = True
OpenUnhideSheets
ThisWorkbook.Sheets("Analysis").Activate

Event_Exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub