Solved

Modifying a Hyerlink to another worksheet

Posted on 2014-01-24
13
177 Views
Last Modified: 2014-01-24
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?
0
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
13 Comments
 
LVL 18

Accepted Solution

by:
Steven Harris earned 334 total points
ID: 39807405
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
 

Author Comment

by:Frank Freese
ID: 39807465
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
 
LVL 18

Assisted Solution

by:Steven Harris
Steven Harris earned 334 total points
ID: 39807474
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39807484
Hi,

The code should be in the ThisWorbook module

Regards
0
 

Author Comment

by:Frank Freese
ID: 39807551
OK...code copied and pasted the in ThisWorkbook. When I click on the hyperlink the new code is never executed?
0
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 166 total points
ID: 39807568
Hi,

Have you closed an reopened your file?

Regards
0
 

Author Comment

by:Frank Freese
ID: 39807584
no...let me try that now
0
 

Author Comment

by:Frank Freese
ID: 39807601
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
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39807623
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
 

Author Comment

by:Frank Freese
ID: 39807654
Got this from Excel:
Not enough system resources to display completely.
This worksheet has multiple hyperlinks BTW. Would that matter?
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39807664
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
 

Author Comment

by:Frank Freese
ID: 39807683
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
 

Author Closing Comment

by:Frank Freese
ID: 39807956
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question