Excel keeps asking for save when user closes system with "Macros Disabled" logic
 

Hi All

I have implemented the approach to (try to) ensure macros are enabled
discussed in this group.

This is working fine for me apart from when the user attempts to close
the workbook

To summarise my implementation of Workbook_BeforeSave :
- I reveal the "Macros Disabled" sheet
- I hide all the other sheets
- I disable events and then perform the save myself
- I then try and tell Excel that I have done the save so dont
save again with:
Me.Saved = True
Cancel = True ' the Cancel paramter from the
Workbook_BeforeSave call
- I then reveal all the sheets again and hide the "Macros
Disabled" sheet so the user is none the wiser and they can then
continue to work on the file

The problem happens when the user wants to close the workbook:
* if they click save then close - no problem
* if they click close first in order to provoke a "Save" popup
dialog (a lot of users use this approach it seems) then click "Yes"
the save happens and my logic runs but then Excel keeps looping with
the "Save" popup until the user clicks "No" rather than "Yes". This
behaviour confuses my users *big* time.

Does anyone have any idea of why Excel still thinks a save is
required? I thought I could get around this with:
Me.Saved = True
Cancel = True ' the Cancel paramter from the
Workbook_BeforeSave call
but this does not seem to be the case in the second scenario explained
above.

Cheers for any ideas. Please feel free to point out any alternative
implementations.

Chrisso