Prev: Saving & Removing VB Code from WB
Next: Total a column (or row0 by highlighting cells
From: Dale on 20 Apr 2010 14:06
I have a worksheet with over 800 cells. Each cell displays a churches
website url. I need to display the actual hyperlink http://www.whatever.com
not the Link name.
example First Baptist Church Nashville. I need the hyperlink
http://fbcnashville.org. displayed in the next cell.
From: Luke M on 20 Apr 2010 14:29
'Here's a short macro:
'Note that this assumes you are not using the function HYPERLINK, but just
an 'embedded hyperlink
'To install, right click on sheet tab, view code, paste the following in.

Sub ListHyperlinks()
i = 1
For Each h In Me.Hyperlinks
'Generates list in column Z, starting at row 1
Cells(i, "Z").Value = h.Name
i = i + 1
Next h
End Sub

--
Best Regards,

Luke M
"Dale" <Dale(a)discussions.microsoft.com> wrote in message
news:ADCADC73-18E6-4642-BCD0-D0E6859B51E5(a)microsoft.com...
>I have a worksheet with over 800 cells. Each cell displays a churches
> website url. I need to display the actual hyperlink
> http://www.whatever.com
> not the Link name.
> example First Baptist Church Nashville. I need the hyperlink
> http://fbcnashville.org. displayed in the next cell.


From: Don Guillett on 20 Apr 2010 14:31
If you have this http://fbcnashville.org/

typed into a cell you can just use
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
ActiveWorkbook.FollowHyperlink Address:=Target
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Dale" <Dale(a)discussions.microsoft.com> wrote in message
news:ADCADC73-18E6-4642-BCD0-D0E6859B51E5(a)microsoft.com...
>I have a worksheet with over 800 cells. Each cell displays a churches
> website url. I need to display the actual hyperlink
> http://www.whatever.com
> not the Link name.
> example First Baptist Church Nashville. I need the hyperlink
> http://fbcnashville.org. displayed in the next cell.

From: JLatham on 20 Apr 2010 14:36
See if this doesn't do the job for you:

Sub ExtractHyperlinks()
'change these Const values as needed
Const theWorksheetName = "Salary.com"
'the column with hyperlink text
Const hLinkColumn = "B"
'column to put link only into
Const hLinkOnlyCol = "AI"

Dim myWS As Worksheet
Dim linksRange As Range
Dim anyLink As Range

Set myWS = Worksheets(theWorksheetName)
Set linksRange = myWS.Range(hLinkColumn & "1:" & _
myWS.Range(hLinkColumn & Rows.Count).End(xlUp).Address)
For Each anyLink In linksRange
If anyLink.Hyperlinks.Count > 0 Then
myWS.Range(hLinkOnlyCol & anyLink.Row) = _
anyLink.Hyperlinks(1).Address
End If
Next
Set linksRange = Nothing
Set myWS = Nothing
MsgBox "Task Completed"
End Sub


"Dale" wrote:

> I have a worksheet with over 800 cells. Each cell displays a churches
> website url. I need to display the actual hyperlink http://www.whatever.com
> not the Link name.
> example First Baptist Church Nashville. I need the hyperlink
> http://fbcnashville.org. displayed in the next cell.
 | 
Pages: 1
Prev: Saving & Removing VB Code from WB
Next: Total a column (or row0 by highlighting cells