Why must I click twice?

[zenahs]

I've created a macro to remind users to complete a specific worksheet within a workbook before closing. A pop-up asks "Have you completed the T2020 (worksheet)?" If the user clicks "No", then close will be cancelled and the user will be able to update the worksheet as required. If the user clicks "Yes", then the workbook will close. Here's my problem:
For some reason, the user must click "Yes" twice before the workbook will close. Or, if the user clicks "Yes" then "No" it will still close. Either way, two mouse clicks are required to close the workbook. Why? Any thoughts?
Thanks in advance for your help!

Here's the macro:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("T2020").Select

x = MsgBox("Have you updated the T2020?", vbYesNo, "T2020 Reminder")

If x = vbYes Then
ThisWorkbook.Close
End
End If
If x = vbNo Then
Cancel = True
End
End If

End Sub

[Tom Ogilvy]

The workbook is already set to close - you don't need to close it and fire
the beforeclose event again.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("T2020").Select

x = MsgBox("Have you updated the T2020?", vbYesNo, "T2020 Reminder")

If x = vbNo Then
Cancel = True
End If

End Sub

--
Regards,
Tom Ogilvy



"zenahs" <zenahs.1y8qtp_1131558912.375@excelforum-nospam.com> wrote in
message news:zenahs.1y8qtp_1131558912.375@excelforum-nospam.com...
>
> I've created a macro to remind users to complete a specific worksheet
> within a workbook before closing. A pop-up asks "Have you completed
> the T2020 (worksheet)?" If the user clicks "No", then close will be
> cancelled and the user will be able to update the worksheet as
> required. If the user clicks "Yes", then the workbook will close.
> Here's my problem:
> For some reason, the user must click "Yes" twice before the workbook
> will close. Or, if the user clicks "Yes" then "No" it will still
> close. Either way, two mouse clicks are required to close the
> workbook. Why? Any thoughts?
> Thanks in advance for your help!
>
> Here's the macro:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Sheets("T2020").Select
>
> x = MsgBox("Have you updated the T2020?", vbYesNo, "T2020 Reminder")
>
> If x = vbYes Then
> ThisWorkbook.Close
> End
> End If
> If x = vbNo Then
> Cancel = True
> End
> End If
>
> End Sub
>
>
> --
> zenahs
> ------------------------------------------------------------------------
> zenahs's Profile:
http://www.excelforum.com/member.php...o&userid=28681
> View this thread: http://www.excelforum.com/showthread...hreadid=483599
>

[Bob Phillips]

You don't need the Close within the script

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("T2020").Select

x = MsgBox("Have you updated the T2020?", vbYesNo, "T2020 Reminder")

If x = vbNo Then
Cancel = True
End
End If

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"zenahs" <zenahs.1y8qtp_1131558912.375@excelforum-nospam.com> wrote in
message news:zenahs.1y8qtp_1131558912.375@excelforum-nospam.com...
>
> I've created a macro to remind users to complete a specific worksheet
> within a workbook before closing. A pop-up asks "Have you completed
> the T2020 (worksheet)?" If the user clicks "No", then close will be
> cancelled and the user will be able to update the worksheet as
> required. If the user clicks "Yes", then the workbook will close.
> Here's my problem:
> For some reason, the user must click "Yes" twice before the workbook
> will close. Or, if the user clicks "Yes" then "No" it will still
> close. Either way, two mouse clicks are required to close the
> workbook. Why? Any thoughts?
> Thanks in advance for your help!
>
> Here's the macro:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Sheets("T2020").Select
>
> x = MsgBox("Have you updated the T2020?", vbYesNo, "T2020 Reminder")
>
> If x = vbYes Then
> ThisWorkbook.Close
> End
> End If
> If x = vbNo Then
> Cancel = True
> End
> End If
>
> End Sub
>
>
> --
> zenahs
> ------------------------------------------------------------------------
> zenahs's Profile:
http://www.excelforum.com/member.php...o&userid=28681
> View this thread: http://www.excelforum.com/showthread...hreadid=483599
>

[zenahs]

Thank you for the quick response. It works perfectly now!

[Gary L Brown]

The first question your macro asks is 'Have you completed the T2020
(worksheet)?'.
The second question is 'Do you want to save the changes you made to ....."
with 'Yes/No/Cancel' options.

If you want the workbook to always save before closing (without asking),
use...

ThisWorkbook.Close SaveChanges:=True

If you NEVER want the workbook to save before closing (without asking), use...

ThisWorkbook.Close SaveChanges:=False

Instead of...
ThisWorkbook.Close

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"zenahs" wrote:

>
> I've created a macro to remind users to complete a specific worksheet
> within a workbook before closing. A pop-up asks "Have you completed
> the T2020 (worksheet)?" If the user clicks "No", then close will be
> cancelled and the user will be able to update the worksheet as
> required. If the user clicks "Yes", then the workbook will close.
> Here's my problem:
> For some reason, the user must click "Yes" twice before the workbook
> will close. Or, if the user clicks "Yes" then "No" it will still
> close. Either way, two mouse clicks are required to close the
> workbook. Why? Any thoughts?
> Thanks in advance for your help!
>
> Here's the macro:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Sheets("T2020").Select
>
> x = MsgBox("Have you updated the T2020?", vbYesNo, "T2020 Reminder")
>
> If x = vbYes Then
> ThisWorkbook.Close
> End
> End If
> If x = vbNo Then
> Cancel = True
> End
> End If
>
> End Sub
>
>
> --
> zenahs
> ------------------------------------------------------------------------
> zenahs's Profile: http://www.excelforum.com/member.php...o&userid=28681
> View this thread: http://www.excelforum.com/showthread...hreadid=483599
>
>