Activating a Worksheet from a Close Window functions as expected but not from an Exit option in an Add-In menubar

Question

I have a Code Workbook and several Data workbooks with multiple worksheets. I need to be able to Activate a particular worksheet when closing a Data workbook. If I use the 'X' in the upper right corner of the Data workbook, the worksheet changes. If I use the Exit option in an Add-In Menu bar, even though the program runs through the same BeforeClose code, it will not Activate the correct worksheet.

The following code is in a code module in the CodeBook.xlsm file:

Option Explicit

Sub Auto_Open()
    'Establish a special menu
    MenuBars(xlWorksheet).Menus.Add Caption:="O&ptions"
    'Create Menu Items
    MenuBars(xlWorksheet).Menus("Options").MenuItems.Add Caption:="Open CodeBook1.xlsm", OnAction:="Open_File"
    MenuBars(xlWorksheet).Menus("Options").MenuItems.Add Caption:="Exit", OnAction:="AutoClose"

End Sub

Sub Open_File()
    Dim sPath As String
    sPath = ThisWorkbook.Path
    Workbooks.Open sPath & "\DataBook1.xlsm"
End Sub

Public Sub AutoClose()
    'See if an Event workbook or the Main workbood called the subroutine
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then
        ActiveWorkbook.Save
        MsgBox "Before ActiveWorkbook.Close"
        ActiveWorkbook.Close
        MsgBox "Back from ActiveWorkbook.Close"
        Exit Sub
    End If
End Sub

The following code is in ThisWorkbook in the DataBook1.xlsm which has a Sheet1 and Sheet2:

Public Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sBk As String
    Dim cApp As Object
    Set cApp = Application
    sBk = ThisWorkbook.Name
    ThisWorkbook.Save
    Worksheets("Sheet2").Activate
    MsgBox "Codebook Workbook Before Close  After Activate Sheet2" & vbNewLine & _
        "ActiveWindow " & ActiveWindow.Caption & vbNewLine & _
        "ThisWorkbook " & ThisWorkbook.Name & vbNewLine & _
        "ActiveWorkbook " & ActiveWorkbook.Name & vbNewLine & _
        "ActiveSheet " & ActiveSheet.Name & vbNewLine & _
        "Display Alerts " & Application.DisplayAlerts & vbNewLine & _
        "Events Enabled " & Application.EnableEvents & vbNewLine & _
        "Screen Updating " & Application.ScreenUpdating
    Worksheets("Sheet1").Activate
    MsgBox "Codebook Workbook Before Close After Activate Sheet1" & vbNewLine & _
        "ActiveWindow " & ActiveWindow.Caption & vbNewLine & _
        "ThisWorkbook " & ThisWorkbook.Name & vbNewLine & _
        "ActiveWorkbook " & ActiveWorkbook.Name & vbNewLine & _
        "ActiveSheet " & ActiveSheet.Name & vbNewLine & _
        "Display Alerts " & Application.DisplayAlerts & vbNewLine & _
        "Events Enabled " & Application.EnableEvents & vbNewLine & _
        "Screen Updating " & Application.ScreenUpdating
'    Cancel = True
End Sub

Private Sub Workbook_Open()
   Worksheets("Sheet1").Activate
End Sub

To make it easier to test, uncomment the Cancel = True in the DataBook ThisWorkbook code.

Nathan_Sav, thanks. I put that line in the Data ThisWorkbook just after the 'Worksheets("Sheet2").Activate' statement. No change. — MaryB
Since using the 'X' is really closing the Window, I tried 'Windows("DataBook1.xlsm").Close' in the AutoClose subroutine instead of the 'ActiveWorkbook.Close'. Also did not work. — MaryB

TechnoDabbler TechnoDabbler · Accepted Answer · 2020-02-18T01:11:14

My testing would suggest that worksheets can't be "activated" from within ThisWorkbook, that is Workbook_BeforeClose etc. The code runs, but nothing happens.

You might be able to overcome this by executing the activate(s) in a normal code module (which sits in the data worksheet) first.

For example, in a (normal) code module in DataBook1.xlsm add:

Option Explicit

Public Sub SwapSheets()
    Worksheets("Sheet2").Activate
    MsgBox "Codebook Workbook Before Close  After Activate Sheet2" & vbNewLine & _
        "ActiveWindow " & ActiveWindow.Caption & vbNewLine & _
        "ThisWorkbook " & ThisWorkbook.Name & vbNewLine & _
        "ActiveWorkbook " & ActiveWorkbook.Name & vbNewLine & _
        "ActiveSheet " & ActiveSheet.Name & vbNewLine & _
        "Display Alerts " & Application.DisplayAlerts & vbNewLine & _
        "Events Enabled " & Application.EnableEvents & vbNewLine & _
        "Screen Updating " & Application.ScreenUpdating
    Worksheets("Sheet1").Activate
    MsgBox "Codebook Workbook Before Close After Activate Sheet1" & vbNewLine & _
        "ActiveWindow " & ActiveWindow.Caption & vbNewLine & _
        "ThisWorkbook " & ThisWorkbook.Name & vbNewLine & _
        "ActiveWorkbook " & ActiveWorkbook.Name & vbNewLine & _
        "ActiveSheet " & ActiveSheet.Name & vbNewLine & _
        "Display Alerts " & Application.DisplayAlerts & vbNewLine & _
        "Events Enabled " & Application.EnableEvents & vbNewLine & _
        "Screen Updating " & Application.ScreenUpdating
End Sub

Then modify your AutoClose in CodeBook.xlsm to call the above first ... before executing the other code.

Public Sub AutoClose()
    'See if an Event workbook or the Main workbood called the subroutine
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then

        ' do the activates here first
        ActiveWorkbook.Application.Run ("'" & ActiveWorkbook.Name & "'!SwapSheets")

        ActiveWorkbook.Save
        ActiveWorkbook.Close
        Exit Sub
    End If
End Sub

Activating a Worksheet from a Close Window functions as expected but not from an Exit option in an Add-In menubar

1 Answers