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

  • Thread starter Thread starter John Fuller
  • Start date Start date
J

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
 
I'm not expert, but I think if you put True after the Workbook.Close
command it should save...

Rob Mandle
 
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.
 
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
 
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.
 
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).
 
Back
Top