Excel 2nd try - Tweak code

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

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

2nd try - Tweak code

Re: 2nd try - Tweak code

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

Check out our Excel VBA Resources