How to run a macro after user has chosen save "yes"/"no" upon closing file

[skylined]

Hi all,

I'm making my first moves in the excel macro world, and having the following difficulty:

I have an excel sheet in which I build in some checks and macros, so therefore I need to ensure that users execute macros when opening the file. I looked around on the internet and found that this is best done by hiding all sheets upon closing of the file. This way, when a user opens it without executing macros, he does not see his file.

The formula:
Sub Hide_All()
Sheet1.Visible = xlSheetVeryHidden
Sheet4.Visible = xlSheetVisible
End Sub

And executed in:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Hide_All
End Sub

Problem is now: say a user works in the sheet, saves it (e.g. using save as) and clicks on the "X" on the right top side of the screen to close the file, he/she gets the question "do you want to save yes/no/cancel".

When taking "yes" or "cancel": no problem.
But when choosing "no", the excel sheet does not log the fact that the sheets have been hidden. And so the next time he/she opens without executing macros, the files that are supposed to be hidden are visible.

So in brief: how can I execute the hide macro even if the user selects "no" as save option upon closing? (perhaps a mandatory save as?)

 

[VoG]

Hello and welcome to MrExcel.

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Hide_All
ThisWorkbook.Save
Application.DisplayAlerts = True
End Sub