Migrating from Excel 2007 to 2013 - odd behaviour for hide / reveal sheet/s

[jkharmer]

Hi,

I am in the process of migrating an Excel spreadsheet from 2007 to 2013. I have had to do a lot of work on conditional formatting as it seems that Microsoft have changed how the conditional formatting engine works from 2007 to 2010 - 2016. Anyway, that is pretty much sorted, but I have noticed another strange bit of behaviour on what is a very simple macro. The idea behind the code below is to simply have a toggle box which then hides or unhides a sheet named "Velocity". This works perfectly.

Sub mac_Tests_Velocity()

' Hides and Unhides Velocity Tab
Application.ScreenUpdating = False
If Sheets("TESTS").Range("p12").Value = 2 Then Sheets("Velocity").Visible = True
If Sheets("TESTS").Range("p12").Value = 1 Then Sheets("Velocity").Visible = False

If Sheets("TESTS").Range("p12").Value = 1 Then Sheets("Report").Range("XNR_VELOCITY").EntireRow.Hidden = True
If Sheets("TESTS").Range("p12").Value = 2 Then Sheets("Report").Range("XNR_VELOCITY").EntireRow.Hidden = False
Application.DisplayAlerts = True

End Sub

The next bit of code should do the same sort of thing, but instead of hiding or unhiding a single sheet, it should do 3 sheets:

Sub mac_Tests_VOCs()

' Hides and Unhides VOCs Tabs
Application.ScreenUpdating = False
If Sheets("TESTS").Range("p48").Value = 2 Then Sheets("VOCs").Visible = True
If Sheets("TESTS").Range("p48").Value = 1 Then Sheets("VOCs").Visible = False
If Sheets("TESTS").Range("p48").Value = 2 Then Sheets("Selected VOCs").Visible = True
If Sheets("TESTS").Range("p48").Value = 1 Then Sheets("Selected VOCs").Visible = False
If Sheets("TESTS").Range("p48").Value = 2 Then Sheets("Charted VOCs").Visible = True
If Sheets("TESTS").Range("p48").Value = 1 Then Sheets("Charted VOCs").Visible = False

If Sheets("TESTS").Range("p48").Value = 1 Then Sheets("Report").Range("XNR_VOCS").EntireRow.Hidden = True
If Sheets("TESTS").Range("p48").Value = 2 Then Sheets("Report").Range("XNR_VOCS").EntireRow.Hidden = False
Application.ScreenUpdating = True

End Sub

All this worked fine in Excel 2007. However, when I run this second macro in Excel 2016 (and 2013), it unhides the 3 sheets, but then jumps to the sheet called "Selected VOCs" (the middle one of the 3). It should not do this, it should stay on the sheet from where the macro was run.

Can anyone shed some light on why this behaviour may be occurring? I have around 30 of these hide / unhide toggle boxes, and the odd behaviour is only occurring when trying to hide / unhide multiple tabs at one time. Interestingly, it only does the sheet jumping thing when unhiding the sheets, it does not jump when toggling back to hiding the sheets.

Thanks, J

 

[jkharmer]

hmm, anyone?