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.

 

[TheWennerWoman]

I'll try that tomorrow when I'm back in work.

I couldn't use MsgBox because I needed customised wording on the buttons and apparently you have to start messing about with the API which wouldn't be possible in a corporate environment.

Thanks for your time, I will feedback

 

[dmt32]

I'll try that tomorrow when I'm back in work.

I couldn't use MsgBox because I needed customised wording on the buttons and apparently you have to start messing about with the API which wouldn't be possible in a corporate environment.

Thanks for your time, I will feedback

Understood & in that case, userform approach much simpler

Dave

 

[TheWennerWoman]

Ok, for whatever reason I couldn't get it to work the way I needed it to. I ended up simply populating a cell based on what button on the userform was clicked (1, 2 or 3) and then bouncing the workbook_close event accordingly.

Very frustrating but I got there in the end.

Thank you all for your input.

 

[dmt32]

Ok, for whatever reason I couldn't get it to work the way I needed it to. I ended up simply populating a cell based on what button on the userform was clicked (1, 2 or 3) and then bouncing the workbook_close event accordingly.

Very frustrating but I got there in the end.

Thank you all for your input.

Hi,
Pleased solution sort of resolved your issue but it was just to resolve cancelling the close event - If you need to return values for each of the buttons then an update using Msgbox enumeration should be straighforward

Private Sub CommandButton1_Click()
'Yes Button
'user has clicked Yes
    Me.Hide
    Me.Tag = vbYes
End Sub

Private Sub CommandButton2_Click()
'Cancel Button
'user has clicked Cancel
    Me.Hide
    Me.Tag = vbCancel
End Sub

Private Sub CommandButton3_Click()
'Finance Button
'user has clicked Finance Use Only
Me.Hide
Me.Tag = vbNo
End Sub

Function GetResponse() As VbMsgBoxResult
    Me.Show
    GetResponse = Val(Me.Tag)
    Unload Me
End Function

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

Updated Close event code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Response As VbMsgBoxResult
    
    
    Response = UserForm10.GetResponse
    
    If Response = vbCancel Then
    
        Cancel = True
        
    ElseIf Response = vbYes Then
        'yes button
    
    Else
        'finance button

    
    End If
    
End Sub

Hope helpful & appreciate feedback

Dave