CODEWITHSUNDEEP
excelvba
Coopermancm
Coopermancm

Reputation: 1

With Excel 2016, VBA will not activate an alternate worksheet when the Macro is associated with a worksheet

I have a Macro that is associated with my "Introduction" sheet. It is triggered by a cell change and then calls a second macro that manipulates another worksheet, "TimeInLibraryData". It's pretty simple:

Private Sub Worksheet_Change(ByVal Target As Range)

    Sheets("TimeInLibraryData").Visible = True
    Sheets("TimeInLibraryData").Activate
    
    MsgBox "The name of the active sheet is " & ActiveSheet.Name
    
    Call CreateTimeLine.CreateTimeLine1(1)

End Sub


Public Sub CreateTimeLine1(PickSingleLib As Long)

    Sheets("TimeInLibraryData").Activate
    
    MsgBox "The name of the active sheet is " & ActiveSheet.Name

End Sub

You can see I am outputting the Active Sheet name. The problem is that in both places shown, I see that the ActiveSheet is the "Introduction" sheet when it should be "TimeInLibraryData"

The application was written in Excel 2010 and I have just updated to Excel 2016 where the problem is seen.

Running in Excel 2016, if I access the CreateTimeLine1 macro during normal runtime, it works. I only see a problem when the Macro is called following a change to the "Introduction" worksheet.

I have created a cut down example in VBA 2016 and found that it works as expected. I also created the simple example in Excel 2010 and ran it in Excel 2016 which also worked.

So - I have a very perplexing situation tied to the running a set of Macros written in Excel/VBA 2010 that is not working correctly in Excel/VBA 2016

Upvotes: 0

Views: 1221

Answers (1)

Excelosaurus
Excelosaurus

Reputation: 2849

I'm using Excel 2016 with the latest patches and can't reproduce the behavior. You should sandwich your call to Sheets("TimeInLibraryData").Activate between Application.EnableEvents = False and Application.EnableEvents = True, to ensure no other event handlers catch the Worksheet_Activate event your line will fire.

Upvotes: 0

Related Questions