Save with a macro - avoid "Do you want to save changes" loop

[spruce56]

Hi All,
I'm working on a macro process that forces users to log in by only showing a landing page when they open the workbook.

In short, when you save:

1. Workbook_BeforeSave event cancels the save and runs two macros, mLockAndSave, and mUnlock instead
2. mLockAndSave hides all worksheets except the landing page
3. mLockAndSave disables events, saves the workbook, and reenables events
4. mUnlock shows all the normal worksheets (so that the user can keep working) and sets the ThisWorkbook.Saved property to True

This works fine if users save changes, and then closes the workbook.

The problem is if there are unsaved changes, and the user tries to close the workbook:

"Do you want to save changes" appears - fine. The user can choose to Save, and the macros run as they should.

But then "Do you want to save changes" appears again in an infinite loop, until the user chooses Don't Save. This happens even if ThisWorkbook.Saved is set to True. I'm not sure what other sort of flag I could set to make the message not appear.

These are the macros:

---

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

'---If this is not the first use of the workbook, cancel the save
If Not pFirstUse = True Then
Cancel = True
'---Lock and save the workbook
mLockAndSave
'---If the user is logged in, unlock the workbook after saving
If pUser <> "" Then
mUnlock
End If
End If
End Sub

---

Sub mUnlock()
Dim aWorksheet
Application.ScreenUpdating = False
For Each aWorksheet In ThisWorkbook.Worksheets
If aWorksheet.Name <> Range("aaUsers").Parent.Name Then
aWorksheet.Visible = xlSheetVisible
End If
Next
Application.ScreenUpdating = True
ThisWorkbook.Saved = True
End Sub

---

Sub mLockAndSave()
Dim aWorksheet
Application.ScreenUpdating = False
Worksheets(Range("aaWelcome").Parent.Name).Visible = xlSheetVisible
For Each aWorksheet In ThisWorkbook.Worksheets
If aWorksheet.Name <> Range("aaWelcome").Parent.Name Then
aWorksheet.Visible = xlVeryHidden
End If
Next
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Norie]

Have you tried stepping through the code when this behaviour occurred?

That might give you some insight on what's happening and, hopefully, give you some idea of how to resolve things.

 

[spruce56]

Have you tried stepping through the code when this behaviour occurred?

That might give you some insight on what's happening and, hopefully, give you some idea of how to resolve things.

Hi Norie - I have tried stepping through, and all of the lines run - but once the macros are finished, the "Do you want to save changes" message reappears. It seems to triggered/looped outside of the code.

I suspect part of the problem is that it detects that in Workbook_BeforeSave, the Cancel argument was set to True. Possibly, as far as Excel is concerned, the workbook was not saved, and so the message has to appear again. I'm just not sure how to tell Excel that the workbook was saved, as it already sets ThisWorkbook.Saved to True.

 

[Norie]

As far as I'm aware setting Cancel to True tells VBA to not proceed with saving the workbook, it doesn't tell it whether the workbook has been save or not.

 

[spruce56]

Another avenue that doesn't solve it (process of elimination! I'm still hoping to fix this.) is setting Application.DisplayAlerts to False, but as soon as the sub ends, this resets to True, and "Do you want to save changes..." pops up again.

I'm wondering about setting a public variable as a flag that detects that the user tried to close the file. If I could detect whether the user chose cancel after that, could include a Thisworkbook.Close False line, or similar.