Workbook_BeforeClose macro question

[imonline]

I need for a message to appear when a user closes a workbook. If the user clicks "YES" it opens a userform. I wrote the code in the "Workbook_BeforeSave" sub and the msg opens fine but when I click "Yes" my userform barely starts and the workbook closes.

Is there a way to suspend the workbook to close until I've finished using my userform.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Msg, Style, Title, Response
Msg = "Do you want to change the date?"   
Style = vbYesNo + vbCritical + vbDefaultButton2   
Title = "Change Date"
   
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then 
Load UserForm1
UserForm1.Show

End If
End Sub

Can someone please help

 

[Smitty]

What's your User Form code?

If you're dismissing the form to have a user change the date on a sheet, then the wb will close unless you add Exit Sub. You could change the date through the form though.

And you can get rid of Load UserForm1 as the two statements together are erroneous.

Hope that helps,

Smitty