2nd try - Tweak code
Posted by Jim on January 28, 2002 5:30 AM
No takers on Friday so posting this again.
Someone on this board helped me a while back with code to do the following:
My workbook has many sheets named in the format ABC1 ABC2 DEF1 DEF2 ... XYZ1 XYZ2 and they appear in this order. The workbook opens with 2 windows side by side. If I choose sheet ABC1 in the left window, I wanted the macro to activate ABC2 in the right window. If I choose ABC2 in the left, macro activates ABC1 in right, and vice versa if I'm choosing in the right window.
The code works but leaves the active window as 2 if I chose in 1 and vice versa. I want the active window to be the one I chose from. This is probably a real simple change but over my head.
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