Excel is Ignoring ThisWorkbook.Save event after Thisworkbook.Close call

[John Fuller]

I have several macros and such that hide all sheets except 1, then
saves, then sets the sheets back to visible. The problem I run into is
if I use the thisworkbook.close function, it ignores the
thisworkbook.save function call when going through and thus does not
save the workbook. I have a macro called Close_This that gets called
by a menu button i've created (I'll spare all that code). When I use
this, it ignores the thisworkbook.save function. If I simply click the
x on the workbook, everything works fine. (And I've stepped through the
code, it definitely gets to the thisworkbook.save event, it just
doesn't save). Any help is greatly appreciated.

- John

Code as below.

*******************************
In the ThisWorkbook page:

Function AreOpen() As Boolean
AreOpen = False

For i = 2 To 3
If Not ThisWorkbook.Worksheets("Resources").Cells(i, 2).Value =
"" Then
AreOpen = True
End If
Next i
End Function

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False

Call Hide_Sheets
ThisWorkbook.Worksheets("Enable Macros").Protect
Password:="tpmodel"
ThisWorkbook.Protect Password:="tpmodel"

Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True

ThisWorkbook.Unprotect Password:="tpmodel"
ThisWorkbook.Worksheets("Enable Macros").Unprotect
Password:="tpmodel"
Call Show_Sheets

ThisWorkbook.Saved = True

Cancel = True

Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
ThisWorkbook.Unprotect Password:="tpmodel"

Call Show_Sheets

Call Set_CurDir
Call Create_Menu
Call Reset_Resources
Worksheets("Throughput Model").Activate
Worksheets("Resources").Visible = False
ThisWorkbook.Saved = True
End Sub

Sub Workbook_BeforeClose(Cancel As Boolean)
If AreOpen() Then
MsgBox "There are models open. Unable to close." &
vbNewLine & "Close the other models and try again.", vbOKOnly,
"Throughput Model"
Cancel = True
Else
If ThisWorkbook.Saved = False Then
doSave = MsgBox("Do you want to save the changes you
made to '" & ThisWorkbook.Name & "'?", vbYesNoCancel + vbExclamation,
"Throughput Model")
If doSave = vbYes Then
Call Delete_Menu
Call Workbook_BeforeSave(False, False)
ElseIf doSave = vbNo Then
Call Delete_Menu
ThisWorkbook.Saved = True
Else
Cancel = True
End If
Else
Call Delete_Menu
End If
End If
End Sub

Private Sub Hide_Sheets()
ThisWorkbook.Worksheets("Enable Macros").Visible = True
ThisWorkbook.Worksheets("Throughput Model").Visible = False
ThisWorkbook.Worksheets("Growth").Visible = False
ThisWorkbook.Worksheets("Business Units").Visible = False
ThisWorkbook.Worksheets("Resources").Visible = False
End Sub

Private Sub Show_Sheets()
ThisWorkbook.Worksheets("Throughput Model").Visible = True
ThisWorkbook.Worksheets("Growth").Visible = True
ThisWorkbook.Worksheets("Business Units").Visible = True
ThisWorkbook.Worksheets("Resources").Visible = False
ThisWorkbook.Worksheets("Enable Macros").Visible = False
End Sub


*******************************
In the Menu Module:

part that sets up the menu button:
Set CustomMenuItem = CustomMenuSub.Controls.Add
With CustomMenuItem
.Caption = "&Throughput Model"
.OnAction = "Close_This"
.BeginGroup = True
.Enabled = True
End With


Sub Close_This()
ThisWorkbook.Close
End Sub

Sub Delete_Menu()
On Error Resume Next
Application.CommandBars("Throughput Model").Delete
Application.CommandBars(1).Controls("File").Controls("Toggle TP
Menu").Delete
Application.CommandBars(1).Controls("File").Controls("Toggle TP
Menu").Delete
On Error GoTo 0
End Sub

 

[rmandle]

I'm not expert, but I think if you put True after the Workbook.Close
command it should save...

Rob Mandle

 

[John Fuller]

It already calls the thisworkbook_beforesave function, so that didn't
fix it. Its a very strange problem. I can save/close using the excel
menu bar. If I use thisworkbook.save in code outside the
thisworkbook_beforesave function, it ignores it. So I have a feeling
it has to do with unresolved thisworkbook.close/save calls getting it
hung up, but I'm not sure how to work around it and get it to save then
close.

 

[rmandle]

Hmmm... Understood... Another thought is that sometimes if you close a
workbook that has the code in it, the program simply stops running.
Could it be that you are closing the workbook too early?

Sorry if I have been no help, I've just been reading others' problems
whiole waiting to see if people were responding to my own question!

Good luck!

Rob

 

[JLGWhiz]

If you want the workbook to be saved, the command must precede the
thisWorkbook.Close command. If you want the workbook to close without
saving, then use the WorkbooksSaved = True statement before the Close command.

 

[John Fuller]

I don't want it to just do one. I want it to ask the user and then do
the sheet hiding/unhiding based on the answer, like its set up to do in
the thisworkbook_beforeclose procedure is now. I guess I may have to
copy/paste that whole procedure into the close_this sub and run it that
way (don't really want to do that though so still looking for a better
solution).