Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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" wrote in message ... 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 |
#3
![]() |
|||
|
|||
![]()
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 |
#4
![]() |
|||
|
|||
![]()
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" wrote in message ... 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 |
#5
![]() |
|||
|
|||
![]()
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 |
#6
![]() |
|||
|
|||
![]()
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!). |
#7
![]() |
|||
|
|||
![]()
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 the 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" wrote in message ... 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 |
#8
![]() |
|||
|
|||
![]()
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" wrote in message ... 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 |
#9
![]() |
|||
|
|||
![]()
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" wrote in message ... 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" wrote in message ... 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 |
#10
![]() |
|||
|
|||
![]()
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" wrote in message ... 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 |
#11
![]() |
|||
|
|||
![]()
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" wrote in message ... 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" wrote in message ... 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 |
#12
![]() |
|||
|
|||
![]()
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" wrote in message ... 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 |
#13
![]() |
|||
|
|||
![]()
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" wrote in message ... 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" wrote in message ... 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 |
#14
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Simple Hyperlink Macro | New Users to Excel | |||
Automatically run Macro | Excel Discussion (Misc queries) | |||
How do I execute command from button or hyperlink? | Excel Discussion (Misc queries) | |||
How can I set up a Macro to automatically run when I open a speci. | Excel Discussion (Misc queries) | |||
Automatically launch a macro when a fil is opened | Excel Discussion (Misc queries) |