1st window determines 2nd
Posted by Jim on November 26, 2001 9:58 AM
I have 2 unrelated tasks I'd like to accomplish. I'll post these as two separate items.
I have a file with many sheets, with tab names abc1, abc2, xyz1, xyz2, etc. The file opens with 2 windows tiled vertically (see previously posted question). If I go to the sheet named xyz1 in the left window, I want the right window to activate and show xyz2. Surely this is doable but I don't know where to start. Thanks for any suggestions.
Posted by cecilia on November 26, 2001 11:30 AM
sorry . its not doable
Posted by Mark O'Brien on November 26, 2001 2:02 PM
This is perfectly doable using VBA.
I hope I understand you correctly when you say "tiled vertically". This is my example. (I'll assume you don't know too much VBA, this will save explanation posts later.)
I opened a Excel and got "Book1". I then went to Window|New Window to give me Windows with the titles "Book1:1" and "Book1:2". I resized them and put them side by side. I then right clicked on the tab for "Sheet1" then chose "View Code". I then put this code on the sheet (you should copy and paste this onto yours):
Private Sub Worksheet_Activate()
Windows("Book1:2").Activate
Worksheet("Sheet2").Activate
End Sub
What this does is -
When "Sheet1" is selected in Window "Book1:1" it activates "Sheet2" and Window "Book1:2". I hope this is analogous to your problem. Obviously all you need to do is to change the names of the Windows and sheets to reflect your application and put the codes in the proper places.
Hope this helps
Mark
Posted by Juan Pablo on November 26, 2001 2:19 PM
Actually, it's doable. Try this one (It worked for me)
It goes in "ThisWorkbook" module.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Nm As String
Application.EnableEvents = False
If ActiveWindow.Parent.Name = ThisWorkbook.Name Then
Select Case ActiveWindow.Index
Case 1
If Right(Sh.Name, 1) = "1" Then
Nm = Sheets(Sh.Index + 1).Name
ThisWorkbook.Windows(2).Activate
Sheets(Nm).Select
Else
Nm = Sheets(Sh.Index - 1).Name
ThisWorkbook.Windows(2).Activate
Sheets(Nm).Select
End If
Case 2
If Right(Sh.Name, 1) = "1" Then
Nm = Sheets(Sh.Index + 1).Name
ThisWorkbook.Windows(1).Activate
Sheets(Nm).Select
Else
Nm = Sheets(Sh.Index - 1).Name
ThisWorkbook.Windows(1).Activate
Sheets(Nm).Select
End If
End Select
Application.EnableEvents = True
End If
End Sub
Juan Pablo
Posted by Jim on November 27, 2001 5:52 AM
Didn't work, but
You understood perfectly what I wanted; however, this code generates a Sub or Function not defined error while highlighting "Worksheet" on the 3rd row. If I follow the logic, this code would have to be placed onto each sheet.
The code offered by Juan Pablo is Workbook level code and also works perfectly, with the added functionality of allowing the user to choose a worksheet in either window with the macro bringing the corresponding sheet up in the other window. YOU GUYS ARE AMAZING!! This is perfectly doable using VBA. I hope I understand you correctly when you say "tiled vertically". This is my example. (I'll assume you don't know too much VBA, this will save explanation posts later.) I opened a Excel and got "Book1". I then went to Window|New Window to give me Windows with the titles "Book1:1" and "Book1:2". I resized them and put them side by side. I then right clicked on the tab for "Sheet1" then chose "View Code". I then put this code on the sheet (you should copy and paste this onto yours): Private Sub Worksheet_Activate()
