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

You can use a simply UDF function


Function GetHyperlink(target As Range) As String

GetHyperlink = target.Text

End Function


On worksheet use
=GetHyperlink(A1)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=152626

Microsoft Office Help

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

A UDF is a user defined Function that can either work like a worksheet
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 GetHyperlink
wil return a character string containing the hyperlink URL.

When calling functions from a worksheet the range of Cells is A1 like
=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 the
worksheet and not from any macros. The Error you posted "User-defined
type not defined" indicates you didn't add to to the actual macro into
the VBA section of the code or didn't put it in the correct location in
VBA.

If you go into the VBA window in Excel and display the VBA Project view
(usually of the right side of the VBA Window). If you don't have the
view go to VBA menu View - Project Explorer. In the explorer there are
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 as
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 module
by going to VBA menu : Insert - Module and paste the three lines into
the View. If there is a module (like module 1) the double click the
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 worksheet
and add the function again. You need to force a change in the worksheet
to force excel to recognized that the function was added by changing a
cel location which causes excel to perform a "Recalculation".

If you get stuck ask more questions.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=152626

Microsoft Office Help

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

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=152626

Microsoft Office Help