Refer to Hyperlink

[Cathy]

How can I refer to the hyperlink information of a cell?

Lets say cell A1 contains text "website"
but the hyperlink refers to "http://www.website.com"

I would like for cell B1 to only show the hyperlink information of
cell A1

Any help will be appreciate

TIA

 

[joel]

You can use a simply UDF function


Function GetHyperlink(target As Range) As String

GetHyperlink = target.Text

End Function


On worksheet use
=GetHyperlink(A1

 

[Cathy]

That seems great, except I have no idea how or where to setup a UDF
function, Any pointers will be appreciated.

I have tried stepping into macros and replaced everything with the
Function you provided above.
Then tried to enter the =GetHyperlink(A1) but get a "Compile Error"
User-defined type not defined.

TIA

 

[joel]

A UDF is a user defined Function that can either work like a workshee
function or get called from macros. It is a function (not a subroutine
because it returns a value. A worksheet function like "=SUM(A1:A10)
will return the sum of the cells as a value. My function GetHyperlin
wil return a character string containing the hyperlink URL.

When calling functions from a worksheet the range of Cells is A1 lik
=GetHyperlink(A1). From VBA you need to use a Range like
GetHyperlink(Range("A1")).

I think from your posting you only need to call the function from th
worksheet and not from any macros. The Error you posted "User-define
type not defined" indicates you didn't add to to the actual macro int
the VBA section of the code or didn't put it in the correct location i
VBA.

If you go into the VBA window in Excel and display the VBA Project vie
(usually of the right side of the VBA Window). If you don't have th
view go to VBA menu View - Project Explorer. In the explorer there ar
3 types of VBA sheets where Subroutines and Functions can be added

1) ThisWorkbook
2) One VBA sheet for each worksheet in the workbook
3) Modules : General purpose VBA sheets. User can add as many a
needed like adding worksheets to a workbook.


You need to add this 3 line function to a module sheet in the workbook

Function GetHyperlink(target As Range) As String
GetHyperlink = target.Text
End Function


If you don't see any Modules in the VBA Project view add a new modul
by going to VBA menu : Insert - Module and paste the three lines int
the View. If there is a module (like module 1) the double click th
module in the VBA Project and paste the 3 lines of code.

After you put the function into the VBA window go back to the workshee
and add the function again. You need to force a change in the workshee
to force excel to recognized that the function was added by changing
cel location which causes excel to perform a "Recalculation".

If you get stuck ask more questions

 

[Cathy]

Thank you for your very clear response. I followed this to the letter
and managed to get the UDF implemented and working.

Unfortunately the result is the text of the cell "website". I wish the
result to show the url the hyperlink refers to (i.e. "http://
www.website.com" )

Thanks again for the great response, I feel like I am getting a lot
closer to getting this working

TIA

 

[joel]

I orignally tested the code using a link to another sheet in the
workbook and it worked properly. My original code didn't on a link to a
webiste. This will work. Modify the one instruction in the macro

from
GetHyperlink = target.Text

to
GetHyperlink = target.Hyperlinks.Item(1).Address

 

[Cathy]

Thank you! Thank you! Thank You! Sooo much!!!

Works a treat. Saves me hours of work.