Before closing workbook event

[TheWennerWoman]

Hello,

I have some code that is triggered when the user clicks the red x to close the workbook. The code opens a form and the user has two options "yes" and "no".

If they click no I just want the form to disappear and they were as they were before it popped up. I tried

Private Sub CommandButton2_Click()
    Cancel = True
    Me.Hide
   
End Sub

but the workbook still closes.

How do I force it to just close the userform down? It's remembering the fact that the red x was clicked.

Many thanks.

 

[dmt32]

Hi,
to prevent the UserForm being closed by pressing red X add this code to your form

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   Cancel = CloseMode = 0
End Sub

Do ensure that you have another method to close the form

example:

Private Sub CommandButton1_Click()
 Unload Me
End Sub

Dave

 

[TheWennerWoman]

Hi, thanks for that but it's not the userform being closed that's my problem.

User tries to close the workbook - a userform pops up giving them a "have you reviewed this data and can vouch for it's accuracy?" with three buttons "Yes", "No", "Finance Use Only".

If they click No, I want the userform to disappear and the workbook to remain open. As it stands, they click No, the form disappears but the workbook still closes.

 

[NoSparks]

I suspect you would need to use the Workbook_BeforeClose event in order to have the Cancel = True to prevent the closing of the workbook.

 

[dmt32]

Hi,
my misunderstanding - You would use the Workbook_BeforeClose event to Cancel the close operation

Dave

 

[TheWennerWoman]

Many thanks both. This is messier than it should be So putting cancel = true in the Workbook_BeforeClose event does the trick if the user clicks No.......except, if the user clicks Yes or Finance Use Only, I'd want the workbook to close! Can I force the workbook to close in the CommandButton1 or CommandButton3 bits of code?

 

[NoSparks]

The Cancel = True in the Workbook_BeforeClose event has nothing to do with your user form.
Call the user form from Workbook_BeforeClose event.
When the user form closes code execution will return to Workbook_BeforeClose.

Put a break point at the start of the Workbook_BeforeClose and continue things with the F8 key to see where/how the code operates.

 

[dmt32]

Many thanks both. This is messier than it should be So putting cancel = true in the Workbook_BeforeClose event does the trick if the user clicks No.......except, if the user clicks Yes or Finance Use Only, I'd want the workbook to close! Can I force the workbook to close in the CommandButton1 or CommandButton3 bits of code?

You can return a value form your userform to do what you want.
Helpful if publish the forms code

Dave

 

[TheWennerWoman]

The Cancel = True in the Workbook_BeforeClose event has nothing to do with your user form.
Call the user form from Workbook_BeforeClose event.
When the user form closes code execution will return to Workbook_BeforeClose.

Put a break point at the start of the Workbook_BeforeClose and continue things with the F8 key to see where/how the code operates.

So Cancel = True in the Workbook_BeforeClose event doesn't cancel the workbook close?

Private Sub Workbook_BeforeClose(Cancel as Boolean)
UserForm10.Show
End Sub

Code in UserForm10 is typical for the three buttons

Private Sub CommandButton1_Click()
'do something here, user has clicked Yes
Me.Hide
ActiveWorkbook.Close SaveChanges:=True
End Sub

Private Sub CommandButton2_Click()
'don't do anything here, user has clicked No
Me.Hide
End Sub

Private Sub CommandButton3_Click()
'do something here, user has clicked Finance Use Only
Me.Hide
ActiveWorkbook.Close SaveChanges:=True
End Sub

Clicking button 1 or 3 keeps the workbook alive when I don't want it to? Clicking button 2 keeps it live (which is good!)

 

[dmt32]

Hi,
try following & see if helps

Code for your UserForm

Private Sub CommandButton1_Click()
'Yes Button
'do something here, user has clicked Yes
    Me.Hide
    Me.Tag = False
End Sub

Private Sub CommandButton2_Click()
'No Button
'don't do anything here, user has clicked No
    Me.Tag = True
    Me.Hide
End Sub

Private Sub CommandButton3_Click()
'Finance Button
'do something here, user has clicked Finance Use Only
Me.Hide
Me.Tag = False
End Sub

Function GetResponse() As Boolean
    Me.Show
    GetResponse = CBool(Me.Tag)
    Unload Me
End Function

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = CloseMode = 0
    'if user clicks red X call No Button
    If Cancel Then Call CommandButton2_Click
End Sub

Code for Workbook Close event

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = UserForm10.GetResponse
End Sub

As an alternative you could use the MsgBox function that has Yes No Cancel buttons

Hope helpful

Dave