Hyperlink TexttoDisplay

[James]

Hi

I hope someone can help with the following issue. I have created an
Excel Spreadsheet which contains a list of UK hills and their Ordnance
Survey grid references. I want the users to be able to click on a
cell containing the grid reference and get a map showing the location.

The cell containing the OS Grid Ref is E21 (E22, E23, etc) I have
created text in cell M21 with
="http://uk.multimap.com/map/places.cgi?quicksearch="&E21

I have then used a function picked up from a web site referenced in
this group.

=HYPERLINK(M21)

The function reads;

Function HyperlinkAddress(cell) As String
If cell.Hyperlinks.Count > 0 Then _
HyperlinkAddress = cell.Hyperlinks(1).Address
End Function

This all works perfectly except it displays an excessively log line of
text;
http://uk.multimap.com/map/places.cgi?quicksearch=SO997246

What I would like is for the cell to just display SO997246 (or
whatever the reference for the current hill is) but still be clickable
and go to the correct page.

Thanks in Advance

James

 

[Tom Ogilvy]

Why reinvent the wheel. Use the built in worksheet function

hyperlink

=hyperlink(M1,E21)

Apparently writing your own has masked the true built in worksheetfunction.
You will probably need to remove your code.

 

[Guest]

James,

When you have run your macro to set up your hyperlinks the text that you
show in the cell can be shortened without affecting the link. You could
construct another macro that runs through the reference cells using the right
function.

<reference cell> = right(<reference cell>,8)

The shortened cell contents should still activate the link.

 

[Guest]

Good call Tom but to work as James intended (I think) you would have to
change your code to

=hyperlink(M1 & E21,E21)

 

[James mcginty]

This post is from a different account of the thread originator

Thanks to all who answered.

Tom's solution was spot on. I think it was a case of not seeing the wood
for the trees. This is the first time I have attempted to manipulate
Hyperlinks programmatically. I therefore did a bit of a search and
found the function created by someone. This led me to believe that there
was not already a built in function so I set off in the wrong direction.

It's always good to get the simple solution.

Thanks again

James

 

[Tom Ogilvy]

Not according to what he showed as the contents of M21

. . . M21 with

="http://uk.multimap.com/map/places.cgi?quicksearch="&E21


--
Regards,
Tom Ogilvy

Good call Tom but to work as James intended (I think) you would have to
change your code to

=hyperlink(M1 & E21,E21)