Cancel "Save Workbook" popup

[RC-]

I have some code that runs during the Workbook_BeforeClose procedure. I
have a Main Menu menu bar that I want to remove when the user closes the
workbook.

The code works with out problem, but, if the user is prompted to save the
workbook before closing AND Cancel is clicked, the menu is removed but the
workbook is still open, the user then does not have access to the menu
options.

I am trying to figure out a way to invoke code that will rebuild the menu
bar if the user clicks Cancel at the save workbook dialog box.

Does anyone know how to capture the Cancel event of the save workbook
procedure?

TIA
RC-

 

[Bob Phillips]

A different approach.

Check if the workbook is 'dirty'

If Not Activeworkbook.Saved

and is so throw up your own message

ans = MsgBox ("Save the workbook", vbYesNoCancel)

and test if they cancel

If ans - vbCancel Then

don't remove your commandbar, and Cancel the close

Cancel = True

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[RC-]

Bob, unfortunately, the code still does not do what I'm looking for. I can
cancel the Workbook_BeforeClose routine, but the Save dialog box still opens
after that routing is done. I found this
http://msdn2.microsoft.com/microsoft.office.tools.excel.workbook.beforeclose.aspx
but that doesn't do what I'm looking for either, any other ideas?

Thanks again,
RC

 

[Bob Phillips]

Show me the code that doesn't work.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[RC-]

It looks like the code you sent originally was just missing the Me.Saved =
True if the user clicked No and Me.Save if the user clicked Yes. The
Me.Saved statement tricks Excel into not prompting to save. Here is the
code that I wrote based on your input:

'Check if the workbook needs to be saved
If ActiveWorkbook.Saved = False Then

'If so, open a dialog box asking to save, not save, or cancel
ans = MsgBox("Do you want to save the changes you made to '" & Me.Name &
"'?", vbYesNoCancel, Me.Parent)

Select Case ans
Case vbCancel: Cancel = True
Case vbYes: Me.Save
Case vbNo: Me.Saved = True
End Select

End If

This works perfectly, thanks for getting me on the right track!

RC-

 

[Bob Phillips]

I was only ripping the code off the top of my head, it wasn't meant to be
complete solution <vbg>

Also I forgot this would be in ThisWorkbook, so you could use Me.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)