Hyperlink Information

[Geoff]

For the active cell, how do you get the target URL information out in VBA.

E.g.

Cell A1 text is "Microsoft"
Cell A1 Hyperlink is http://www.microsoft.com

How do you get out the target URL http://www.microsoft.com

Activell.Hyperlink(1).?????

I have tried various options and nothing works for me. Any help would be
welcome.

 

[Candice Mesk]

The Insert Hyperlink dialog box allows you to enter 'Text to display' which
will be shown in the cell in Excel. The hyperlink url will be hidden if you
enter something different in the Text to display box, though it will still
work as before.

 

[Geoff]

I need to retrieve hyperlink using VBA.

The Insert Hyperlink dialog box allows you to enter 'Text to display' which
will be shown in the cell in Excel. The hyperlink url will be hidden if you
enter something different in the Text to display box, though it will still
work as before.

 

[Mike A]

Try:

ActiveCell.Hyperlinks.Item(1).Address

For the active cell, how do you get the target URL information out in VBA.

E.g.

Cell A1 text is "Microsoft"
Cell A1 Hyperlink is http://www.microsoft.com

How do you get out the target URL http://www.microsoft.com

Activell.Hyperlink(1).?????

I have tried various options and nothing works for me. Any help would be
welcome.

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers

 

[Dave Peterson]

You could use Mike's suggestion in a UserDefinedFunction:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Geoff]

Thanks all for the help

Try:

ActiveCell.Hyperlinks.Item(1).Address





Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers