Open another workbook on current workbook beforeclose event

    I'm wanting to automatically open another workbook using the BeforeClose event for the current workbook. I tried the following code and it works except the "current" work book doesn't close, and the file menu for the second workbook is deactivated so that workbook can't be closed.


    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)

Answer1 = MsgBox("Update the Dashboard?", vbYesNo)

    If Answer1 = vbYes Then
        Workbooks.Open FileName:= "path to filename/filename.xlsx"
    End If

End Sub

    I'm sure I'm just missing something simple.


    Thanks for your help.

    Dave

    There is nothing in your existing macro to cause the issues you are experiencing.


    Two things I would try ....


    #1: Add another command at the end of your macro, telling Excel to close the first workbook. Redundant I know but ???


    #2: Don't utilize the "Workbook_BeforeClose(Cancel As Boolean)". Create a new macro called "CloseMyWB" or something and

    place all your macro commands in there ... including closing the first workbook.

    Try this


    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)

Answer1 = MsgBox("Update the Dashboard?", vbYesNo)

    If Answer1 = vbYes Then
        Workbooks.Open Filename:="path to filename/filename.xlsx"
        ThisWorkbook.Close True '<- TRUE will save the current workbook's changes, FALSE will close without saving.
    End If

End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login