Multiple Monitors - tab selection

[Dman333]

I have an issue with multiple monitors and maintaining selected tabs when running some code that hides and unhides sheets. I have a “Dashboard” setup so the user can include or exclude sheets from the consolidation summaries (not in the uploaded file). This is a critical part of the model for scenario analysis. Many of the users have 2-4 monitors and like to open the workbook multiple times so they can see the results on various output tabs when changing assumptions inputs.

The problem is when the window with the “Dashboard” tab is open on screen 2, the tab in screen 1 changes to the last open tab from the code – meaning it will jump from the original tab being viewed to the last tab opened. So, this means every time the user opens a new set of sheets, the user has to manually select the tab they were on before selecting “yes” in the dashboard, which starts to code to unhide the sheet. This is only an issue when changing it from “no” to “yes” in the dashboard and when the dashboard is in monitor 2.

Anyone know of a way to control this in Vba. The code is in the “Dashboard” sheet. I’m hoping there’s a way to call the tab selected before executing the code and then selecting after the code is run for each monitor – much the way you find calculation method, turn off and then back to what it was before. The tab selected in monitor 1 changes once the tab is made visible.

I don't know how to upload the whole file - so please let me know if there's a way to do that and I will. The code is below.

Any suggestions are greatly appreciated and thank you in advance for your time and efforts. I hope this makes sense. If not, let me know if you have any questions.


Cheers,

Dman333

Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim Cel As Range

Dim WS_Count As Integer
Dim i As Integer
Dim WS_Cur As Worksheet
Dim WS_Name As String
Dim CalcMode As Integer

ScreenUpdating = False
Set KeyCells = Range("H9:H13")
Set Cel = ActiveCell

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
ScreenUpdating = False
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

If Range("H9") = "Yes" Then
Application.Sheets("C1 Input").Visible = True
Application.Sheets("C1 Spreads").Visible = True
Else
Application.Sheets("C1 Input").Visible = False
Application.Sheets("C1 Spreads").Visible = False
End If
If Range("H10") = "Yes" Then
Application.Sheets("C2 Input").Visible = True
Application.Sheets("C2 Spreads").Visible = True
Else
Application.Sheets("C2 Input").Visible = False
Application.Sheets("C2 Spreads").Visible = False
End If
If Range("H11") = "Yes" Then
Application.Sheets("C3 Input").Visible = True
Application.Sheets("C3 Spreads").Visible = True
Else
Application.Sheets("C3 Input").Visible = False
Application.Sheets("C3 Spreads").Visible = False
End If
If Range("H12") = "Yes" Then
Application.Sheets("C4 Input").Visible = True
Application.Sheets("C4 Spreads").Visible = True
Else
Application.Sheets("C4 Input").Visible = False
Application.Sheets("C4 Spreads").Visible = False
End If
If Range("H13") = "Yes" Then
Application.Sheets("C5 Input").Visible = True
Application.Sheets("C5 Spreads").Visible = True
Else
Application.Sheets("C5 Input").Visible = False
Application.Sheets("C5 Spreads").Visible = False
End If
End If

WS_Count = ActiveWorkbook.Worksheets.Count ' Set WS_Count equal to the number of worksheets in the active workbook.'

For i = 1 To WS_Count ' Begin the loop.
WS_Name = ActiveWorkbook.Worksheets(i).Name
If Worksheets(WS_Name).Visible = False Then _
Worksheets(WS_Name).EnableCalculation = False _
Else If Worksheets(WS_Name).Visible = True Then _
Worksheets(WS_Name).EnableCalculation = True
Next i

Cel.Select
Application.Calculation = CalcMode
ScreenUpdating = True
End Sub

 

[Candyman8019]

Since opening multiple instances of the sheet likely append a number to each for example Workbook.xlsm; Workbook (1).xlsm; etc, you could try activating the original workbook at the top of your code. Something like this for example:

workbooks("{your workbook name.xlsm}").sheets("{required sheet name}").activate

 

[Dman333]

Since opening multiple instances of the sheet likely append a number to each for example Workbook.xlsm; Workbook (1).xlsm; etc, you could try activating the original workbook at the top of your code. Something like this for example:

workbooks("{your workbook name.xlsm}").sheets("{required sheet name}").activate

Thanks a ton for your time on this. I'm a novice at best in Vba and getting over my head here. I think I'm tracking this, but have some questions. The code above is in a worksheet tab and is only for hiding and unhiding sheets. If someone has 2-3 instances of the workbook open on 2-3 different screens, will this code take all the screens to the same sheet? As a novice, that's what it looks like with the sheet name - activate syntax. The problem is I won't know which instance of the workbook or what screen the user will be on when they make the changes to this tab. And obviously they would be on different tabs on each screen. So, will this keep all different tabs on their respective screens if I add this? I hope this makes sense.

Lastly, I would need to get the filename and tab name at the beginning of the code as those can change as the file is updated. Any ideas for some quick code to get those in vba so I can set them to a variable and then use that (I think that would be the correct way to do it)?

Thanks again
Dman333

 

[Candyman8019]

The first thing I would ask you is when you open multiple copies of the workbook what do the file names look like at the top of the excel instance? My setup won’t allow me to open the exact same file multiple times.

 

[Dman333]

Yes - Typically users are getting there by going to window - new window. They don't open the file from scratch.

 

[Candyman8019]

I see. In that case, try something like this:

Workbooks("{your workbook name.xlsm}").Windows(1).Activate
Sheets("{required sheet name}").Activate

changing the windows index to 2, 3, etc would activate subsequent windows that may be open. If you try to activate an index that doesn't exist you will get an error.