Execute Macro automatically after hyperlink

I have set up hyperlinks in a table of contents that jumps to specific
categories (individual cells) on the next worksheet. I'd like to have a
macro execute automatically after the link to have that cell be situated at
the upper left of the screen. I'm just beginning to get into macro's, and
have not been able to figure this one out, even after checking other posts on
this site. Thanks in advance!
--
William Elerding

Right click on the tab of the worksheet with the hyperlinks and pick View
Code. In the module that appears paste this code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.Goto ActiveCell, True
End Sub

--
Jim
"Bill Elerding" <[email protected]> wrote in message
news:[email protected]...
>I have set up hyperlinks in a table of contents that jumps to specific
> categories (individual cells) on the next worksheet. I'd like to have a
> macro execute automatically after the link to have that cell be situated
> at
> the upper left of the screen. I'm just beginning to get into macro's, and
> have not been able to figure this one out, even after checking other posts
> on
> this site. Thanks in advance!
> --
> William Elerding

I think I'd just drop the hyperlink and use a macro directly.

Put a bunch of references in A1:A10 of a worksheet.
like:
sheet2!a5
'sheet 99'!b99
'this is a test'!c12

Excel uses the apostrophe to force a value to be text. I actually typed in:

''this is a test'!c12

But excel didn't show that first apostrophe in the cell.

Then rightclick on the worksheet tab and choose View Code.

Paste this in:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, _
Cancel As Boolean)

Dim testRng As Range

If target.Cells.Count > 1 Then Exit Sub

If Intersect(target, Me.Range("a1:A10")) Is Nothing Then Exit Sub

Set testRng = Nothing
On Error Resume Next
Set testRng = Application.Range(target.Value)
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Beep '?
Else
Application.Goto testRng, scroll:=True
Cancel = True
End If

End Sub

This looks for a double click on that cell.


Bill Elerding wrote:
>
> I have set up hyperlinks in a table of contents that jumps to specific
> categories (individual cells) on the next worksheet. I'd like to have a
> macro execute automatically after the link to have that cell be situated at
> the upper left of the screen. I'm just beginning to get into macro's, and
> have not been able to figure this one out, even after checking other posts on
> this site. Thanks in advance!
> --
> William Elerding

--

Dave Peterson

Or this will work with XL97, if placed in the module
behind the sheet with the hyperlinks...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Hyperlinks.Count > 0 Then
ActiveWindow.ScrollColumn = ActiveCell.Column
ActiveWindow.ScrollRow = ActiveCell.Row
End If
End Sub

It also positions the sheet with the 'cell' in the top left corner.

Jim Cone
San Francisco, USA


"Bill Elerding" <[email protected]> wrote in message
news:[email protected]...
> I have set up hyperlinks in a table of contents that jumps to specific
> categories (individual cells) on the next worksheet. I'd like to have a
> macro execute automatically after the link to have that cell be situated at
> the upper left of the screen. I'm just beginning to get into macro's, and
> have not been able to figure this one out, even after checking other posts on
> this site. Thanks in advance!
> --
> William Elerding

I think I like Jim's approach better (a lot better!).

Dave Peterson wrote:
>
> I think I'd just drop the hyperlink and use a macro directly.
>
> Put a bunch of references in A1:A10 of a worksheet.
> like:
> sheet2!a5
> 'sheet 99'!b99
> 'this is a test'!c12
>
> Excel uses the apostrophe to force a value to be text. I actually typed in:
>
> ''this is a test'!c12
>
> But excel didn't show that first apostrophe in the cell.
>
> Then rightclick on the worksheet tab and choose View Code.
>
> Paste this in:
>
> Option Explicit
> Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, _
> Cancel As Boolean)
>
> Dim testRng As Range
>
> If target.Cells.Count > 1 Then Exit Sub
>
> If Intersect(target, Me.Range("a1:A10")) Is Nothing Then Exit Sub
>
> Set testRng = Nothing
> On Error Resume Next
> Set testRng = Application.Range(target.Value)
> On Error GoTo 0
>
> If testRng Is Nothing Then
> 'do nothing
> Beep '?
> Else
> Application.Goto testRng, scroll:=True
> Cancel = True
> End If
>
> End Sub
>
> This looks for a double click on that cell.
>
> Bill Elerding wrote:
> >
> > I have set up hyperlinks in a table of contents that jumps to specific
> > categories (individual cells) on the next worksheet. I'd like to have a
> > macro execute automatically after the link to have that cell be situated at
> > the upper left of the screen. I'm just beginning to get into macro's, and
> > have not been able to figure this one out, even after checking other posts on
> > this site. Thanks in advance!
> > --
> > William Elerding
>
> --
>
> Dave Peterson

--

Dave Peterson

Too many Jim's. I meant Jim Rech's routine (no offense, Mr. Cone).

Dave Peterson wrote:
>
> I think I like Jim's approach better (a lot better!).
>

Thanks, Jim and Dave! I right clicked the page that has the hyperling
origin, and got the 'view code' screen. When I got the 'Sheet 1 code'
screen, this is what was there:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub


I copied your macro over this, and 'Sheet 1 code' now shows this:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Hyperlinks.Count > 0 Then
ActiveWindow.ScrollColumn = ActiveCell.Column
ActiveWindow.ScrollRow = ActiveCell.Row
End If
End Sub


When I click onto the hyperlinks, it goes there, but does not situate the
cell to the upper left of the screen. Have I missed something? Thanks for
all your help!!!


"Jim Rech" wrote:

> Right click on the tab of the worksheet with the hyperlinks and pick View
> Code. In the module that appears paste this code:
>
> Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> Application.Goto ActiveCell, True
> End Sub
>
> --
> Jim
> "Bill Elerding" <[email protected]> wrote in message
> news:[email protected]...
> >I have set up hyperlinks in a table of contents that jumps to specific
> > categories (individual cells) on the next worksheet. I'd like to have a
> > macro execute automatically after the link to have that cell be situated
> > at
> > the upper left of the screen. I'm just beginning to get into macro's, and
> > have not been able to figure this one out, even after checking other posts
> > on
> > this site. Thanks in advance!
> > --
> > William Elerding
>
>
>

Whoops, Jim. The way it shows on the 'Sheet 1 code' is:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.Goto ActiveCell, True
End Sub


I had tried the option presented by Mr. Cone, also, with the same result. I
guess it is a little late at night (midnight), and I'm doing something wrong
if both approaches do not work for me. Thanks again, all!

"Jim Rech" wrote:

> Right click on the tab of the worksheet with the hyperlinks and pick View
> Code. In the module that appears paste this code:
>
> Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> Application.Goto ActiveCell, True
> End Sub
>
> --
> Jim
> "Bill Elerding" <[email protected]> wrote in message
> news:[email protected]...
> >I have set up hyperlinks in a table of contents that jumps to specific
> > categories (individual cells) on the next worksheet. I'd like to have a
> > macro execute automatically after the link to have that cell be situated
> > at
> > the upper left of the screen. I'm just beginning to get into macro's, and
> > have not been able to figure this one out, even after checking other posts
> > on
> > this site. Thanks in advance!
> > --
> > William Elerding
>
>
>

You did put the code in the module of the sheet with the hyperlinks, not the
sheet you want to go to, right? That's the only reason I can see that the
code wouldn't work.

--
Jim
"Bill Elerding" <[email protected]> wrote in message
news:[email protected]...
> Whoops, Jim. The way it shows on the 'Sheet 1 code' is:
>
> Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> Application.Goto ActiveCell, True
> End Sub
>
>
> I had tried the option presented by Mr. Cone, also, with the same result.
> I
> guess it is a little late at night (midnight), and I'm doing something
> wrong
> if both approaches do not work for me. Thanks again, all!
>
> "Jim Rech" wrote:
>
>> Right click on the tab of the worksheet with the hyperlinks and pick View
>> Code. In the module that appears paste this code:
>>
>> Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
>> Application.Goto ActiveCell, True
>> End Sub
>>
>> --
>> Jim
>> "Bill Elerding" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have set up hyperlinks in a table of contents that jumps to specific
>> > categories (individual cells) on the next worksheet. I'd like to have
>> > a
>> > macro execute automatically after the link to have that cell be
>> > situated
>> > at
>> > the upper left of the screen. I'm just beginning to get into macro's,
>> > and
>> > have not been able to figure this one out, even after checking other
>> > posts
>> > on
>> > this site. Thanks in advance!
>> > --
>> > William Elerding
>>
>>
>>

Jim Rech's code worked fine for me (xl2003) if I had a hyperlink that was
inserted via insert|hyperlink.

And you pasted the code under the worksheet that held the hyperlinks, right?

Bill Elerding wrote:
>
> Whoops, Jim. The way it shows on the 'Sheet 1 code' is:
>
> Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> Application.Goto ActiveCell, True
> End Sub
>
> I had tried the option presented by Mr. Cone, also, with the same result. I
> guess it is a little late at night (midnight), and I'm doing something wrong
> if both approaches do not work for me. Thanks again, all!
>
> "Jim Rech" wrote:
>
> > Right click on the tab of the worksheet with the hyperlinks and pick View
> > Code. In the module that appears paste this code:
> >
> > Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> > Application.Goto ActiveCell, True
> > End Sub
> >
> > --
> > Jim
> > "Bill Elerding" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have set up hyperlinks in a table of contents that jumps to specific
> > > categories (individual cells) on the next worksheet. I'd like to have a
> > > macro execute automatically after the link to have that cell be situated
> > > at
> > > the upper left of the screen. I'm just beginning to get into macro's, and
> > > have not been able to figure this one out, even after checking other posts
> > > on
> > > this site. Thanks in advance!
> > > --
> > > William Elerding
> >
> >
> >

--

Dave Peterson

Hi, Jim. Yes I put it on the first sheet (Table of Contents), with the
hyperlinks that go to the second worksheet (Data). This is what the code now
says of the first sheet (Table of Contents):


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.Goto ActiveCell, True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub


Is there anything I need to do to the two drop-down boxes at the top of the
code box?

The hyperlinks are working both as I originally wrote them, and also when I
use the insert hyperlink to a second page called 'Data'. It does seem that
the active cell is at either the top or bottom of the screen, but not to the
upper left. I'm clearly missing something obvious, as I can not use this
code if I also try it in a different worksheet. Yet, I know it works for
both you Dave.

Thanks...


"Jim Rech" wrote:

> You did put the code in the module of the sheet with the hyperlinks, not the
> sheet you want to go to, right? That's the only reason I can see that the
> code wouldn't work.
>
> --
> Jim
> "Bill Elerding" <[email protected]> wrote in message
> news:[email protected]...
> > Whoops, Jim. The way it shows on the 'Sheet 1 code' is:
> >
> > Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> > Application.Goto ActiveCell, True
> > End Sub
> >
> >
> > I had tried the option presented by Mr. Cone, also, with the same result.
> > I
> > guess it is a little late at night (midnight), and I'm doing something
> > wrong
> > if both approaches do not work for me. Thanks again, all!
> >
> > "Jim Rech" wrote:
> >
> >> Right click on the tab of the worksheet with the hyperlinks and pick View
> >> Code. In the module that appears paste this code:
> >>
> >> Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> >> Application.Goto ActiveCell, True
> >> End Sub
> >>
> >> --
> >> Jim
> >> "Bill Elerding" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have set up hyperlinks in a table of contents that jumps to specific
> >> > categories (individual cells) on the next worksheet. I'd like to have
> >> > a
> >> > macro execute automatically after the link to have that cell be
> >> > situated
> >> > at
> >> > the upper left of the screen. I'm just beginning to get into macro's,
> >> > and
> >> > have not been able to figure this one out, even after checking other
> >> > posts
> >> > on
> >> > this site. Thanks in advance!
> >> > --
> >> > William Elerding
> >>
> >>
> >>
>
>
>

Thanks, Jim! Took me a while to work out some of 'my' bugs, especially with
the concatenation. Have a great evening.
--
William Elerding


"Jim Cone" wrote:

> Or this will work with XL97, if placed in the module
> behind the sheet with the hyperlinks...
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
> If Target.Hyperlinks.Count > 0 Then
> ActiveWindow.ScrollColumn = ActiveCell.Column
> ActiveWindow.ScrollRow = ActiveCell.Row
> End If
> End Sub
>
> It also positions the sheet with the 'cell' in the top left corner.
>
> Jim Cone
> San Francisco, USA
>
>
> "Bill Elerding" <[email protected]> wrote in message
> news:[email protected]...
> > I have set up hyperlinks in a table of contents that jumps to specific
> > categories (individual cells) on the next worksheet. I'd like to have a
> > macro execute automatically after the link to have that cell be situated at
> > the upper left of the screen. I'm just beginning to get into macro's, and
> > have not been able to figure this one out, even after checking other posts on
> > this site. Thanks in advance!
> > --
> > William Elerding
>

Thanks, Jim. It's taken me a while to work out the bugs on my side. I had
some contatenated fields feeding the hyperlinks that were guming up the
works. Once I did that, both your and Daves approach worked very well! I
really appreciate the help.
--
William Elerding


"Jim Rech" wrote:

> You did put the code in the module of the sheet with the hyperlinks, not the
> sheet you want to go to, right? That's the only reason I can see that the
> code wouldn't work.
>
> --
> Jim
> "Bill Elerding" <[email protected]> wrote in message
> news:[email protected]...
> > Whoops, Jim. The way it shows on the 'Sheet 1 code' is:
> >
> > Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> > Application.Goto ActiveCell, True
> > End Sub
> >
> >
> > I had tried the option presented by Mr. Cone, also, with the same result.
> > I
> > guess it is a little late at night (midnight), and I'm doing something
> > wrong
> > if both approaches do not work for me. Thanks again, all!
> >
> > "Jim Rech" wrote:
> >
> >> Right click on the tab of the worksheet with the hyperlinks and pick View
> >> Code. In the module that appears paste this code:
> >>
> >> Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> >> Application.Goto ActiveCell, True
> >> End Sub
> >>
> >> --
> >> Jim
> >> "Bill Elerding" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have set up hyperlinks in a table of contents that jumps to specific
> >> > categories (individual cells) on the next worksheet. I'd like to have
> >> > a
> >> > macro execute automatically after the link to have that cell be
> >> > situated
> >> > at
> >> > the upper left of the screen. I'm just beginning to get into macro's,
> >> > and
> >> > have not been able to figure this one out, even after checking other
> >> > posts
> >> > on
> >> > this site. Thanks in advance!
> >> > --
> >> > William Elerding
> >>
> >>
> >>
>
>
>

Thanks, Dave. As I mentioned to Jim Rech, both of your approaches worked
very well once I cleaned up my bugs. Seems contatenating the cells for a
hyperlink created some issues on my part. Life is good once again...

Have a good evening!
--
William Elerding


"Dave Peterson" wrote:

> I think I like Jim's approach better (a lot better!).
>
> Dave Peterson wrote:
> >
> > I think I'd just drop the hyperlink and use a macro directly.
> >
> > Put a bunch of references in A1:A10 of a worksheet.
> > like:
> > sheet2!a5
> > 'sheet 99'!b99
> > 'this is a test'!c12
> >
> > Excel uses the apostrophe to force a value to be text. I actually typed in:
> >
> > ''this is a test'!c12
> >
> > But excel didn't show that first apostrophe in the cell.
> >
> > Then rightclick on the worksheet tab and choose View Code.
> >
> > Paste this in:
> >
> > Option Explicit
> > Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, _
> > Cancel As Boolean)
> >
> > Dim testRng As Range
> >
> > If target.Cells.Count > 1 Then Exit Sub
> >
> > If Intersect(target, Me.Range("a1:A10")) Is Nothing Then Exit Sub
> >
> > Set testRng = Nothing
> > On Error Resume Next
> > Set testRng = Application.Range(target.Value)
> > On Error GoTo 0
> >
> > If testRng Is Nothing Then
> > 'do nothing
> > Beep '?
> > Else
> > Application.Goto testRng, scroll:=True
> > Cancel = True
> > End If
> >
> > End Sub
> >
> > This looks for a double click on that cell.
> >
> > Bill Elerding wrote:
> > >
> > > I have set up hyperlinks in a table of contents that jumps to specific
> > > categories (individual cells) on the next worksheet. I'd like to have a
> > > macro execute automatically after the link to have that cell be situated at
> > > the upper left of the screen. I'm just beginning to get into macro's, and
> > > have not been able to figure this one out, even after checking other posts on
> > > this site. Thanks in advance!
> > > --
> > > William Elerding
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Dave Peterson
>