Right-mouse open a 2nd file to a particular sheet that correspondswith a 1st file's sheet

[michaelrlanier]

I have 2 files: FileA and FileB. FileA is an office. FileB is a help file used to explain data in FileA. Both have 10 sheets.

Here is what I hope to accomplish. If I am viewing FileA, Sheet 3, I want to right mouse and have FileB open to its Sheet 3. Likewise, I need this corresponding action for all 10 sheets. I am using Excel 2003. Is this possible? Any suggested macro would be appreciated. Thanks.

Michael

 

[Auric__]

I have 2 files: FileA and FileB. FileA is an office. FileB is a help
file used to explain data in FileA. Both have 10 sheets.

Here is what I hope to accomplish. If I am viewing FileA, Sheet 3, I
want to right mouse and have FileB open to its Sheet 3. Likewise, I need
this corresponding action for all 10 sheets. I am using Excel 2003. Is
this possible? Any suggested macro would be appreciated. Thanks.

Put this in FileA's ThisWorkbook object:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Workbooks("FileB").Activate
Workbooks("FileB").Sheets(Sh.Name).Activate
Cancel = True
End Sub

Note that if an appropriately-named sheet isn't in FileB you'll get an error
(Error 9, "Subscript out of range"). You can change Sheets(Sh.Name) to
something else to get around that, but I'll leave that as an exercise for the
student. (In other words, I can't remember how offhand, and I'm too tired to
bother investigating.)

Note also (important!) that this will completely prevent the right-click menu
from ever showing in FileA unless you use the keyboard.

 

[michaelrlanier]

Thank you Auric. You have been quite helpful.

Michael