Cancel "Save Workbook" popup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RC- - 19 Jan 2006 18:39 GMT

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-

Reply to this Message

Bob Phillips - 19 Jan 2006 18:55 GMT

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

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> 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
[quoted text clipped - 13 lines]
> TIA
> RC-

Reply to this Message

RC- - 19 Jan 2006 21:03 GMT

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

>A different approach.
>
[quoted text clipped - 32 lines]
>> TIA
>> RC-

Reply to this Message

Bob Phillips - 19 Jan 2006 21:14 GMT

Show me the code that doesn't work.

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> 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?
>
[quoted text clipped - 37 lines]
> >> TIA
> >> RC-

Reply to this Message

RC- - 19 Jan 2006 21:27 GMT

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-

> Show me the code that doesn't work.
>
[quoted text clipped - 50 lines]
>> >> TIA
>> >> RC-

Reply to this Message

Bob Phillips - 19 Jan 2006 21:57 GMT

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.

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> 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
[quoted text clipped - 27 lines]
> > 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?
> >>
[quoted text clipped - 41 lines]
> >> >> TIA
> >> >> RC-

Reply to this Message