Worksheet Navigation

I have a workbook with 12 worksheets. I want to be able to hyperlink from
one worksheet to another, then use a command button to return to the previous
button (i.e. fire HL on WS1 which goes to specific location on WS2, then fire
a macro via command button to return to original location in WS1). Not sure
how to do this. Can you pass variables to/from PSubs in the same module?

First, in ThisWorkbook's code module, put in this event procedure:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target
As Hyperlink)
Set LinkBack = Target.Range
End Sub

LinkBack is a Global variable defined in a separate code module:
Public LinkBack as Range

Finally, the code for the buttons would be like this:
Sub Button1_Click()
LinkBack.Parent.Activate ' to activate the source sheet
LinkBack.Activate ' to activate the source range
End Sub

"subseaguy" wrote:

> I have a workbook with 12 worksheets. I want to be able to hyperlink from
> one worksheet to another, then use a command button to return to the previous
> button (i.e. fire HL on WS1 which goes to specific location on WS2, then fire
> a macro via command button to return to original location in WS1). Not sure
> how to do this. Can you pass variables to/from PSubs in the same module?

OK.. let me make sure I understand...
On WS1 (where I am initiating HL) tab, "View Code" and insert

Public LinkBack as Range
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target
As Hyperlink)
Set LinkBack = Target.Range
End Sub

Then in workbook, insert code for button;
Sub Button1_Click()
LinkBack.Parent.Activate ' to activate the source sheet
LinkBack.Activate ' to activate the source range
End Sub

Have I understood your suggestion?

Thanks,
SubseaGuy

"K Dales" wrote:

> First, in ThisWorkbook's code module, put in this event procedure:
>
> Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target
> As Hyperlink)
> Set LinkBack = Target.Range
> End Sub
>
> LinkBack is a Global variable defined in a separate code module:
> Public LinkBack as Range
>
> Finally, the code for the buttons would be like this:
> Sub Button1_Click()
> LinkBack.Parent.Activate ' to activate the source sheet
> LinkBack.Activate ' to activate the source range
> End Sub
>
> "subseaguy" wrote:
>
> > I have a workbook with 12 worksheets. I want to be able to hyperlink from
> > one worksheet to another, then use a command button to return to the previous
> > button (i.e. fire HL on WS1 which goes to specific location on WS2, then fire
> > a macro via command button to return to original location in WS1). Not sure
> > how to do this. Can you pass variables to/from PSubs in the same module?

Not quite: The main requirement is that the Workbook_SheetFollowHyperlink
Sub must go in ThisWorkbook's code module. That is, when you are in the VB
editor, make sure you can see the Project Explorer and double-click on the
line that says "ThisWorkbook." You can make sure you are typing it in the
right place by checking the title bar of the VB editor: it should say
something like "Microsoft Visual Basic - Book1 - [ThisWorkbook (Code)]" The
reason the sub must be in here is that it is an event procedure that runs
automatically whenever the specified event takes place - in this case
whenever a hyperlink is followed. But that operates for the workbook as a
whole and so it has to be in the code module for the workbook for it to be
recognized and to function.

For the Button_Click code, the easiest thing to do is create the button from
the forms toolbar and then when the "Assign Macro" dialog comes up, specify
"New..." It will create a new module and you can type the code in there. But
you can also type that code in any module (ThisWorkbook, Sheet1, ...) and
manually assign the macro to your button as long as you make it a Public Sub.

Same thing with the global variable LinkBack - it can go in any module as
long as it is declared Public.

--
- K Dales


"subseaguy" wrote:

> OK.. let me make sure I understand...
> On WS1 (where I am initiating HL) tab, "View Code" and insert
>
> Public LinkBack as Range
> Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target
> As Hyperlink)
> Set LinkBack = Target.Range
> End Sub
>
> Then in workbook, insert code for button;
> Sub Button1_Click()
> LinkBack.Parent.Activate ' to activate the source sheet
> LinkBack.Activate ' to activate the source range
> End Sub
>
> Have I understood your suggestion?
>
> Thanks,
> SubseaGuy
>
> "K Dales" wrote:
>
> > First, in ThisWorkbook's code module, put in this event procedure:
> >
> > Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target
> > As Hyperlink)
> > Set LinkBack = Target.Range
> > End Sub
> >
> > LinkBack is a Global variable defined in a separate code module:
> > Public LinkBack as Range
> >
> > Finally, the code for the buttons would be like this:
> > Sub Button1_Click()
> > LinkBack.Parent.Activate ' to activate the source sheet
> > LinkBack.Activate ' to activate the source range
> > End Sub
> >
> > "subseaguy" wrote:
> >
> > > I have a workbook with 12 worksheets. I want to be able to hyperlink from
> > > one worksheet to another, then use a command button to return to the previous
> > > button (i.e. fire HL on WS1 which goes to specific location on WS2, then fire
> > > a macro via command button to return to original location in WS1). Not sure
> > > how to do this. Can you pass variables to/from PSubs in the same module?

KD,

I did what you said and I get an "Object Required" error (424). Any thoghts?

"subseaguy" wrote:

> I have a workbook with 12 worksheets. I want to be able to hyperlink from
> one worksheet to another, then use a command button to return to the previous
> button (i.e. fire HL on WS1 which goes to specific location on WS2, then fire
> a macro via command button to return to original location in WS1). Not sure
> how to do this. Can you pass variables to/from PSubs in the same module?