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

M

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
 
A

Auric__

michaelrlanier said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top