VBA Before close

[stuartmacdonald]

I'm looking to give users an option when closing a workbook and have put in the following VBA code, but I cant figure out how to trigger the opening of another workbook if 'yes' is selected.

Private Sub Workbook_BeforeClose(Cancel As Boolean)    If MsgBox("Do you need to refresh timesheets?", _
            vbQuestion + vbYesNo) = vbNo Then
        Cancel = True
    End If
End Sub

I need to add in an, if yes


Workbooks.Open "C:\Users\Stuart MacDonald\OneDrive - Cles\Order book\Refresh.xlsm"

 

[Joe4]

Try something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)    
    If MsgBox("Do you need to refresh timesheets?", _
            vbQuestion + vbYesNo) = vbNo
        Cancel = True
    Else
        Workbooks.Open "C:\Users\Stuart MacDonald\OneDrive - Cles\Order book\Refresh.xlsm"
    End If
End Sub

or

Private Sub Workbook_BeforeClose(Cancel As Boolean)    
    Select Case MsgBox("Do you need to refresh timesheets?", _
            vbQuestion + vbYesNo) = vbNo Then
        Case vbYes
            Workbooks.Open "C:\Users\Stuart MacDonald\OneDrive - Cles\Order book\Refresh.xlsm"
        Case vbNo
            Cancel = True
        Case Else
            'what to do if they do not select Yes or No
    End Select
End Sub

 

[stuartmacdonald]

The first version works, it opens the workbook ut it's not closing the original file when you press either Yes or No.....

 

[Joe4]

The first version works, it opens the workbook ut it's not closing the original file when you press either Yes or No.....

I had a minor typo in the second code (forgot to remove the word "Then").
The first code won't close it when you press "No" because you have "Cancel = True", which means to cancel the Close command (I was using the safe logic you used originally).

It sounds like maybe you want it to close no matter what they choose, just if they choose "Yes", you want the other file opened. If that is the case, try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If MsgBox("Do you need to refresh timesheets?", _
            vbQuestion + vbYesNo) = vbYes Then
        Workbooks.Open "C:\Users\Stuart MacDonald\OneDrive - Cles\Order book\Refresh.xlsm"
    End If
End Sub

 

[stuartmacdonald]

Correct - I want it to close no matter what they choose, just if they choose "Yes", the other file opened.

The code above closes the workbook if you choose 'no', but does not if you choose 'yes'

Whats missing?

 

[Joe4]

Did you copy and paste the code I posted in my last post, verbatim?
It works for me. Regardless of whether I select "Yes" or "No", the workbook that this macro is contained in will close.