Cancel "Save Workbook" popup
 

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-

Cancel "Save Workbook" popup
 

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-" wrote in message
...
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-

Cancel "Save Workbook" popup
 

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...reclose. aspx
but that doesn't do what I'm looking for either, any other ideas?

Thanks again,
RC


"Bob Phillips" wrote in message
...
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-" wrote in message
...
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-

Cancel "Save Workbook" popup
 

Show me the code that doesn't work.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RC-" wrote in message
...
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...reclose. aspx
but that doesn't do what I'm looking for either, any other ideas?

Thanks again,
RC


"Bob Phillips" wrote in message
...
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-" wrote in message
...
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-

Cancel "Save Workbook" popup
 

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" wrote in message
...
Show me the code that doesn't work.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RC-" wrote in message
...
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...reclose. aspx
but that doesn't do what I'm looking for either, any other ideas?

Thanks again,
RC


"Bob Phillips" wrote in message
...
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-" wrote in message
...
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-

Cancel "Save Workbook" popup
 

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)

"RC-" wrote in message
...
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" wrote in message
...
Show me the code that doesn't work.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RC-" wrote in message
...
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...reclose. aspx
but that doesn't do what I'm looking for either, any other ideas?

Thanks again,
RC


"Bob Phillips" wrote in message
...
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-" wrote in message
...
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-