Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink TexttoDisplay
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.cg...earch=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink TexttoDisplay
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. -- Regards, Tom Ogilvy "James" wrote in message om... 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.cg...earch=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink TexttoDisplay
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink TexttoDisplay
Good call Tom but to work as James intended (I think) you would have to
change your code to =hyperlink(M1 & E21,E21) "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink TexttoDisplay
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink TexttoDisplay
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 "dowensan" wrote in message ... Good call Tom but to work as James intended (I think) you would have to change your code to =hyperlink(M1 & E21,E21) "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't make hyperlink function work for hyperlink to website | Excel Worksheet Functions | |||
How do I create a hyperlink to a cell with the hyperlink function | Excel Worksheet Functions | |||
Moving rows with Hyperlink doesn't move hyperlink address | Excel Discussion (Misc queries) | |||
Intra-workbook hyperlink: macro/function to return to hyperlink ce | Excel Discussion (Misc queries) | |||
reading html when hyperlink address not hyperlink text diplayed | Excel Programming |