Need to run a macro when user saves the file + Y/N textbox & no loop

[nico0503]

Hi All,

Quick presentation as it's my first post on this forum: I'm 28 and working in Controlling for a large French company in Singapore. I've been reading this forum for a few years now and always managed to use existing threads to find my way with my codes. However I could not find what I wanted this time. I consider myself as a pretty good excel user but quite an inexperienced VBA user. So I apologize in my request seems basic to you.

I have a file that needs macro to work well. So I have a spreadsheet displaying a message and tutorial to enable it. I need this spreadsheet to be visible and active whenever the user opens the file with macro disabled, but hidden when he opens the file with macro enabled (this part is fine I have my code).

However when in use (so with this "warning" spreadsheet hidden already), in want it to become visible and active again whenever the file is saved and closed.

My expecting result would be something like this:
- When user wants to save the file the "warning" spreadsheet becomes visible and active
- then the file gets saved
- then a Y/N textbox opens asking the user if he wants to continue working on the file
- if yes then the "warning" spreadsheet gets hidden again and the macro ends here (but file should not be saved again at that stage)
- if no then the file should close without asking the user if he wants to save it again (as he just did it before). This is where I had my issues with a new box popping-up.

In that the file cannot be saved without the "warning" being visible and active.

I unfortunately deleted my code so I cannot post it here.

Thanks for your help.

Nicolas

 

[HotRhodium]

I believe what you are looking for would be something like this in the ThisWorkbook module in Excel Objects. These can be tricky to work with and you will have to write your macros to hide and unhide sheets. works very fast you will not even notice it working in the background if done rite.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 With Application
  .EnableEvents = False
  .ScreenUpdating = False
  HideAllButWarning
  .EnableEvents = True
  .ScreenUpdating = True
 End With
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 With Application
  .EnableEvents = False
  .ScreenUpdating = False
  HideAllButWarning
  ThisWorkbook.Save
  Cancel = True
  UnHideSheets
  .EnableEvents = True
  .ScreenUpdating = True
 End With
End Sub
Private Sub Workbook_Open()
 With Application
  .EnableEvents = False
  .ScreenUpdating = False
  UnHideSheets
  .EnableEvents = True
  .ScreenUpdating = True
 End With
End Sub
Public Sub HideAllButWarning()
 'Write a Macro to hide all but your warning sheet
End Sub
Public Sub UnHideAll()
 'Unhide all but your warning sheet
End Sub

 

[nico0503]

Hi HotRhodium,

First of all thanks a lot for your answer! It's very neat and worked 90% well! I spent some time on it and manage to change it slightly to give the exact result I need.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableEvents = False
.ScreenUpdating = False
HideAllButWarning
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Application
.EnableEvents = False
.ScreenUpdating = False
HideAllButWarning
ThisWorkbook.Save
Cancel = True
End With

Dim Msg As String, Ans As Variant

Msg = "Would you like continue working on this file?"
Ans = MsgBox(Msg, vbYesNo)

Select Case Ans
Case vbYes

With Application
.EnableEvents = False
.ScreenUpdating = False
Sheets("Setting").Visible = True
Sheets("Synthesis").Visible = True
'etc for all sheets you want to
Sheets("MACRO").Visible = False
.EnableEvents = True
.ScreenUpdating = True
End With

Case vbNo
GoTo Quit:
End Select

Quit:

End Sub


Public Sub HideAllButWarning()
Sheets("MACRO").Visible = True
Sheets("Setting").Visible = False
Sheets("Synthesis").Visible = False
Sheets("Top_25").Visible = False
Sheets("Full").Visible = False
Sheets("CARS").Visible = False
Sheets("CM").Visible = False
Sheets("LM").Visible = False
Sheets("LY").Visible = False
Sheets("Database").Visible = False
End Sub

 

[nico0503]

sorry i push the button before finishing the edit...

 

[nico0503]

So here is my new code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableEvents = False
.ScreenUpdating = False
HideAllButWarning
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Application
.EnableEvents = False
.ScreenUpdating = False
HideAllButWarning
ThisWorkbook.Save
Cancel = True
End With

Dim Msg As String, Ans As Variant

Msg = "Would you like to stay on this file?"
Ans = MsgBox(Msg, vbYesNo)

Select Case Ans
Case vbYes

With Application
.EnableEvents = False
.ScreenUpdating = False
Sheets("Setting").Visible = True
Sheets("Synthesis").Visible = True
'idem for all sheets that need to be displayed in the file
Sheets("WARNING").Visible = False
.EnableEvents = True
.ScreenUpdating = True
End With

Case vbNo
GoTo Quit:
End Select

Quit:

End Sub


Public Sub HideAllButWarning()
Sheets("WARNING").Visible = True
Sheets("Setting").Visible = False
Sheets("Synthesis").Visible = False
'idem for all sheets that need to be displayed in the file
End Sub

And this is the I wrote in the module to make sure the correct spreadsheets appear when the file is opened with macro unabled

Sub Autpen()
With Application
.EnableEvents = False
.ScreenUpdating = False
Sheets("Setting").Visible = True
Sheets("Synthesis").Visible = True
Sheets("Top_25").Visible = True
'idem for all sheets that need to be displayed in the file
Sheets("WARNING").Visible = False
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub



Thanks a lot again for your great help!!! :D

Nicolas