Problems with explicit .SAVE within a BEFORECLOSE event 1

[tmktech]

This one's got me stumped. I have an EXCEL VBA app that "clean-up" code in a Workbook_BeforeSave event. It unprotects the workbook, hides certain worksheets and make an "Intro" sheet visible, and then protects the workbook again.

This code (which I'll be happy to share if needed), works fine if I do an EXPLICIT SAVE from the menu.

I also have code in the Workbook_BeforeCLOSE event that also procedurally saves the workbook (thus invoking the BeforeSave event for the "clean-up" code). THIS IS WHERE THINGS GO HAYWIRE.

The same code that worked with the explicit save, now does NOT when invoked from a procedural save within the BeforeClose event????

Everything from workbook protecting & unprotecting to setting the visibility of sheets doesn't work.

Strangley enough, while in debug mode within the BeforeSave event, I can go to the menubar thru Tools and Unprotect (or protect) the workbook. Although once unprotected I still can't change the visibility of sheets or activate a different sheet. When I do any of these commands in the immediate window (or stepping thru my code in debug), I get no errors; they're just ignored UNTIL I crash trying to select a cell on an inactive sheet (the sheet.activate was immediately prior, but ignored????)


Sorry for the long post, but I wanted to give all the facts. I sure hope this is something simple that I'm not seeing late at night.

Thanks in advance!

TMKTECH

 

[Bowers74]

Show us your BeforeClose event. You don't have to show it all, I would just like to see the code before making a suggestion.



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[RobBroekhuis]

Consider putting all these actions into an independent sub, and calling the sub from both the beforesave and beforeclose event handlers. You may need to set enableevents to false in the beforeclose handler just before calling the save method, to avoid the mess you're experiencing now.


Rob

 

[tmktech]

Thanks for the reply. You got it.

Here's the code up to the save. Below is also the relevant code in the BeforeSave event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo Err_Handler

If ThisWorkbook.Saved = False Then
save_msg = "Save changes to " & ThisWorkbook.Name & " ?"
save_wkbk = MsgBox(save_msg, vbYesNoCancel, "Save Confirmation&quot
If save_wkbk = vbYes Then
ThisWorkbook.Saved = True
ThisWorkbook.Save
ElseIf save_wkbk = vbCancel Then
Cancel = True
Exit Sub
Else
'mark workbook saved after closing reference files so
'not asked to save again at close.
ThisWorkbook.Saved = True
End If
End If


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Application.EnableCancelKey = xlErrorHandler
error_source = "Before Save"
On Error GoTo Err_Handler
Application.EnableEvents = False
Dim Sh As Worksheet
ThisWorkbook.Unprotect (wksh_pswd)
For Each Sh In Worksheets
If Sh.Name = "Intro" Then
Sh.Visible = True
Else
Sh.Visible = xlSheetVeryHidden
End If
Next

Call Color_Palette
Intro.Activate
Intro.Range("F11&quot.Select 'hide active cell behind an image

 

[tmktech]

RobB,

First of all, it's great to hear from you again! Hope all is well.

Second. Once again, you've solved my problem. Here's a star

All the best to you!

TMKTECH