One of those bang your head against the wall ones...

FYI - ExcelXP, if it matters.

Everything i read says that setting Cancel = True in Workbook_BeforeClose
should prevent the workbook from actually closing, i.e. prevent the built-in
"Do you want to save?..." dialog from popping up. Am I wrong? I have a
blank workbook with only the following code in ThisWorkbook. If a change is
made and the user tries to close the workbook and then clicks CANCEL to my
home-grown dialog box, the built-in File-Save dialog box still pops up right
afterwards. What am i missing?? Thanks.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then
Msg = "Do you want to save the workbook?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Me.Save
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
End Select
End If
End Sub

Your code worked fine for me.

--
Regards,
Tom Ogilvy



"jforstrom" wrote:

One of those bang your head against the wall ones...

FYI - ExcelXP, if it matters.

Everything i read says that setting Cancel = True in Workbook_BeforeClose
should prevent the workbook from actually closing, i.e. prevent the built-in
"Do you want to save?..." dialog from popping up. Am I wrong? I have a
blank workbook with only the following code in ThisWorkbook. If a change is
made and the user tries to close the workbook and then clicks CANCEL to my
home-grown dialog box, the built-in File-Save dialog box still pops up right
afterwards. What am i missing?? Thanks.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then
Msg = "Do you want to save the workbook?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Me.Save
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
End Select
End If
End Sub

Your code worked for me also, and I'm running XP (Excel 2003)

"jforstrom" wrote:

One of those bang your head against the wall ones...

FYI - ExcelXP, if it matters.

Everything i read says that setting Cancel = True in Workbook_BeforeClose
should prevent the workbook from actually closing, i.e. prevent the built-in
"Do you want to save?..." dialog from popping up. Am I wrong? I have a
blank workbook with only the following code in ThisWorkbook. If a change is
made and the user tries to close the workbook and then clicks CANCEL to my
home-grown dialog box, the built-in File-Save dialog box still pops up right
afterwards. What am i missing?? Thanks.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then
Msg = "Do you want to save the workbook?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Me.Save
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
End Select
End If
End Sub

Thanks Mickey and Tom for trying out the code. I ran it from my home
computer last night and it also runs fine. I tried again on my work computer
this morning and it still does not work. Maybe something is fishy with my
Excel installation. The IT group has recently pushed out some Excel add-ins
that may be messing things up. Problem is, i've uninstalled them all and i
still have the issue.

Does anyone even know what sort of setting might be overwritten that would
prevent me from canceling Workbook_BeforeClose? I don't even know where to
begin. If i were home i would just re-install Excel but that's not so easy
at work.

Thanks.

"mickey" wrote:

Your code worked for me also, and I'm running XP (Excel 2003)

"jforstrom" wrote:

One of those bang your head against the wall ones...

FYI - ExcelXP, if it matters.

Everything i read says that setting Cancel = True in Workbook_BeforeClose
should prevent the workbook from actually closing, i.e. prevent the built-in
"Do you want to save?..." dialog from popping up. Am I wrong? I have a
blank workbook with only the following code in ThisWorkbook. If a change is
made and the user tries to close the workbook and then clicks CANCEL to my
home-grown dialog box, the built-in File-Save dialog box still pops up right
afterwards. What am i missing?? Thanks.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then
Msg = "Do you want to save the workbook?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Me.Save
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
End Select
End If
End Sub