Stop Excel from changing active sheet on Sheets.Visible = True command

0
votes

I have a large workbook with many sheets used for background calculation that I hide when they are not in use. The workbook is locked down (no ribbon, sheet tabs, formula bar, etc) so it cannot be tampered with when it is in use.

In Excel 2010, everything went smoothly and the active sheet was not changed, but now in Excel 2016 the following Sub changes the active sheet to the "CompCalc" sheet which leaves the user with no way to return to the sheet they need to be on to use the workbook. 

Sub MakeSheetsVisible(Status As Boolean)
Dim VarSubName As String
VarSubName = "MakeSheetsVisible"
'***********************************************************

ProtectSheets (False)
Sheets("DATA_INPUT").Visible = Status
Sheets("RAW_DATA").Visible = Status
Sheets("MASTERHISTORY").Visible = Status
Sheets("CompCalc").Visible = Status
'Sheets("Event_Enter").Visible = Status
Sheets("Raw_Chart_Data").Visible = Status

End Sub

This Sub is called at the end of a Sub that is called from another Sub which can be triggered 1 of 2 ways, on a button press or ListView double click. The active sheet is only changed in the above routine when the button is used to call the initial Sub and when the routine is ran continuously (not stepped through with F8).

I can work around this issue by checking the original active sheet at the beginning of the routine and setting it back to the original at the end, but I would like to know why this is happening and if there is a way to stop it without a workaround. Any help is appreciated

2
vbaexcelexcel-2016
What are you using activesheet for? You most likely want to use sheet objects instead of using .Activesheet for making changes to cell values and other items. You may want to post the code that is using activesheet and we can help you eliminate the activesheet usage. - MatthewD
@MatthewD Its not that I am using ActiveSheet to make changes to anything after the sheet changes. The issue is that a random sheet is shown with no way for the user to get back to the sheet they should be seeing because the workbook is locked down. - JerryT

2 Answers

0
votes

It's an annoying bug in Excel 2013/2016 and as far as I know, there is no fix. A workaround I use is:

Set CurrentSheet = ActiveSheet
'Instructions here
CurrentSheet.Activate
0
votes

I only ever use active sheet if it's needed for something specific. (as per MatthewD)

But surely when you've set all your sheets to visible, you can add another line to make the sheet you want to be active?

Sheets("The one you want").Activate

would do the job? (or maybe .select?)