MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Ivan F Moala on January 28, 2002 7:31 AM

try this ??

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
ThisWorkbook.Windows(2).Activate

Else
Nm = Sheets(Sh.Index - 1).Name
ThisWorkbook.Windows(2).Activate
Sheets(Nm).Select
ThisWorkbook.Windows(2).Activate
End If
Case 2
If Right(Sh.Name, 1) = "1" Then
Nm = Sheets(Sh.Index + 1).Name
ThisWorkbook.Windows(1).Activate
Sheets(Nm).Select
ThisWorkbook.Windows(1).Activate
Else
Nm = Sheets(Sh.Index - 1).Name
ThisWorkbook.Windows(1).Activate
Sheets(Nm).Select
ThisWorkbook.Windows(1).Activate
End If
End Select
Application.EnableEvents = True
End If
End Sub

ivan

Posted by Jim on January 28, 2002 9:03 AM

Perfect, Ivan. Thanks, I knew it would be something simple.