Modifying a Hyerlink to another worksheet

Folks,
I am using a command button to move me to a specific location on a different worksheet. Here's the code and it works fine:
Application.Goto Sheets ("Text").Range ("A47"),True

Open in new window

Currently I am using a hyperlink, and it works. See code:
=HYPERLINK("[Excel Formulas and Functions.xlsm]TEXT!A199","TEXT - Guidelines for Adding Spaces")

Open in new window

Is it possible to modify the hyperlink to work like the command button does by having the Range select go to the top?
Frank FreeseAsked:
Who is Participating?
 
Steven HarrisConnect With a Mentor PresidentCommented:
You can add in a macro that will make the hyperlink perform as the button:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Application.Goto Reference:=ActiveCell, Scroll:=True
End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
This time you've stumped me.
My hyperlink is created in the formula bar.
I've pasted your code into my worksheet that contains the hyperlink formula. No change so it tells me that I am unable to personally connect the dots as you are suggesting. I don't know how to connect the hyperlink formula with the suggested macro?
0
 
Steven HarrisConnect With a Mentor PresidentCommented:
The code will go into the "This Workbook" sheet in the VB Editor. You will not need to modify your hyperlink.

What this does is capture the action of clicking on a hyperlink, then process the request as "Application.Goto" where you scroll the linked location to the top of the page.

If this still doesn't work with the manual hyperlink formula, try using the right-click > Insert > Hyperlink version to insert a link.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Rgonzo1971Commented:
Hi,

The code should be in the ThisWorbook module

Regards
0
 
Frank FreeseAuthor Commented:
OK...code copied and pasted the in ThisWorkbook. When I click on the hyperlink the new code is never executed?
0
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

Have you closed an reopened your file?

Regards
0
 
Frank FreeseAuthor Commented:
no...let me try that now
0
 
Frank FreeseAuthor Commented:
ok
Closed Excel and reopened the file
Clicked on the hyperlink and all I get is some weird video and the icon going around and around in circles
I'm going to shut down the laptop and then try again.
0
 
Steven HarrisPresidentCommented:
If this still doesn't work with the manual hyperlink formula, try using the right-click > Insert > Hyperlink version to insert a link into the cell.
0
 
Frank FreeseAuthor Commented:
Got this from Excel:
Not enough system resources to display completely.
This worksheet has multiple hyperlinks BTW. Would that matter?
0
 
Steven HarrisPresidentCommented:
This worksheet has multiple hyperlinks BTW. Would that matter?

Shouldn't matter.

Not enough system resources to display completely.
What size is the file?

You can also check some simple steps that has helped others with this error, such as setting all page zoom levels to the same size.  Why it would matter is beyond me...
0
 
Frank FreeseAuthor Commented:
6mb
Something else is going on though. I removed the code from ThisWorkbook, saved the workbook, restarted the notebook and went back to the hyperlink function that I as using. The problem is the same. Fortunately, I saved this file as a revision first so I have the original file. Let me snoop through this worksheet and get back with you all
0
 
Frank FreeseAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.