Hyperlink base and internal links don't work together??

[BVHis]

Just when I think I've got this stuff figured out... WHAM!! I ge
kicked in the *&@# and left gasping for air.

My *wonderful* spreadsheet (which is going to drive me to drink) ha
some hyperlinks; some pointing to various network drives, some pointin
to other sheets within the workbook.

I originally left the hyperlink base blank, but found out that when
moved the XLS to another location, all my links became "relative".

So, to fix the problem, I set the Hyperlink Base to a network drive.
Seemed to work just fine until I clicked on a link to another shee
within the workbook. Now THOSE don't work.


Is there anyway to have the best of both worlds??!?


Thanks in advance to whoever can help me!!

PS: I'm an Excel newbie, so please, be gentle.


~ Matt

 

[Guest]

I have been trying to get an answer to this question for months, but I haven't found it yet. I also have seen other posts of this same question, but no resolution. I don't think this list is the place to try. If you do find something, please post. I will extend the same courtesy.

 

[Dick Kusleika]

Matt

So, to fix the problem, I set the Hyperlink Base to a network drive.
Seemed to work just fine until I clicked on a link to another sheet
within the workbook. Now THOSE don't work.


Is there anyway to have the best of both worlds??!?

Nope. Hyperlinks in Excel stink, in my opinion. The hyperlink base can be
useful, but only in a few, very particular situations. When you have a mix
of internal and external hyperlinks and you use the hyperlink base, Excel
prepends the internal links with the base and opens Windows Explorer -
stupid.

If you really want the best of both worlds, you're in for a lot of work.
The only way that I've been able to get this close to usable, is to use the
FollowHyperlink event. The way that I did it was to point all the
hyperlinks to the cell in which they reside. So if you have a hyperlink in
cell B10, you point the hyperlink to B10. Doing that basically does nothing
at all - except fire the FollowHyperlink event.

Then what I did was put the actual place I wanted to go in the ScreenTip
box. When the FollowHyperlink event was fired, I used the ScreenTip to
actually go to that location. Here's an example.

In B10, insert a hyperlink with these specs

Place In This Document - B10
TTD - Dick's Blog
ScreenTip - http://www.dicks-blog.com

In B11, insert a hyperlink thusly

Place In This Document - B11
TTD - Go to Sheet2
ScreenTip - #Sheet2!A10

Now right click on the sheet's tab and choose View Code. Paste this code
into the resulting code pane.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Me.Parent.FollowHyperlink Target.ScreenTip

End Sub

Now when you click on the hyperlink, it will go nowhere, but it will fire
this event. The event uses what you've put in the screen tip to go to the
correct location. I generally do this for all my hyperlinks, even the ones
that work. You certainly wouldn't have to, though. You could write your
event macro to only process those hyperlinks with, say, a certain keyword in
the screen tip. Whatever you do, this will give you maximum flexibility
and, unfortunately, maximum effort on your part.

Let me know if this sounds good and you need more help with it.