Hide sheets/objects, Save, Unhide Sheets/Objects

There was another post about this recently. I have pieced my code together with that and the help doc. here is what I got.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strCurrentSheet As String
strCurrentSheet = ActiveSheet.Name
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

If Hidden_Elements_Hidden = False Then Call Hidden_Elements_Hide
If SaveAsUI Then ThisWorkbook.SaveAs Application.GetSaveAsFilename Else ThisWorkbook.Save
If Hidden_Elements_Hidden = True Then Call Hidden_Elements_Show
Sheets(strCurrentSheet).Activate

'NOTE THIS LINE
Cancel = True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Workbooks(ThisWorkbook.Name).Saved = True

End Sub


This works fine if I manually save the workbook and then manually close it.
Although, if I just goto close the following happens. I get the save changes prompt. After it saves, I get that same prompt again.

Any Ideas?

Edit:
Is there a way to test if the document is closing in the beforesave event?

Off the top of my head, I'd say implement the _BeforeClose event, check which one fires first, and then set a flag (module-scope variable) in the first event handler which you can read in the second. That should allow you to avoid the double-prompt.

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

If Hidden_Elements_Hidden = False Then Call Hidden_Elements_Hide
If SaveAsUI Then ThisWorkbook.SaveAs Application.GetSaveAsFilename Else ThisWorkbook.Save
ThisWorkbook.Close
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strCurrentSheet As String
strCurrentSheet = ActiveSheet.Name
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

If Hidden_Elements_Hidden = False Then Call Hidden_Elements_Hide
If SaveAsUI Then ThisWorkbook.SaveAs Application.GetSaveAsFilename Else ThisWorkbook.Save
If Hidden_Elements_Hidden = True Then Call Hidden_Elements_Show
Sheets(strCurrentSheet).Activate

'NOTE THIS LINE
Cancel = True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Workbooks(ThisWorkbook.Name).Saved = True

End Sub


Thanks

It's great that it works, but what you're doing there looks a little scary. If you close the workbook (not the application) and the _BeforeSave event isn't run, the application is left in the NoScreenUpdating/EventsDisabled state, i.e. it would be unresponsive.

I see, well I changed it to this.


Private OnClose As Boolean
Private Sub Workbook_BeforeClose(Cancel As Boolean)
OnClose = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strCurrentSheet As String
strCurrentSheet = ActiveSheet.Name
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

If Hidden_Elements_Hidden = False Then Call Hidden_Elements_Hide
If SaveAsUI Then ThisWorkbook.SaveAs Application.GetSaveAsFilename Else ThisWorkbook.Save
If OnClose = True Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
ThisWorkbook.Close
End If
If Hidden_Elements_Hidden = True Then Call Hidden_Elements_Show
Sheets(strCurrentSheet).Activate

Cancel = True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Workbooks(ThisWorkbook.Name).Saved = True

End Sub



That should be ok.

*nods* Better! :)

well, I was accually having a problem with that too. But I seem to have figured it out.
For some reason, excel did not like me closing the workbook in the beforesave. This works tho, for some reason.


Private OnClose As Boolean
Private Sub Workbook_BeforeClose(Cancel As Boolean)
OnClose = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strCurrentSheet As String
strCurrentSheet = ActiveSheet.Name
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

If Hidden_Elements_Hidden = False Then Call Hidden_Elements_Hide
If SaveAsUI Then ThisWorkbook.SaveAs Application.GetSaveAsFilename Else ThisWorkbook.Save
If OnClose = True Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Exit Sub
End If
If Hidden_Elements_Hidden = True Then Call Hidden_Elements_Show
Sheets(strCurrentSheet).Activate

Cancel = True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Workbooks(ThisWorkbook.Name).Saved = True

End Sub



I am getting no errors with that. Everything seems to be working.
The only hich i can see is if some trys to close the workbook. They get the save promtp but cancel. The beforeclose event comes before the beforesave. So the OnClose variable is set to true, before the prompt to save / cancel.

That makes sense, I made no allowance for the user choosing cancel in the save-as dialog in the other thread you were referring to. You seem to be moving along quite well on your own, so I'll not make any suggestions as to how to deal with that case for now. :)