Friday 29 April 2016

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

No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...