Cell Hyperlink - text, screen tip & link from other cells

[Matt Jensen]

Howdy
I want a cell in my spreadsheet to have text come from cell A1 (which is
easy to do obviously), a 'screen tip/text to display' from cell B1, and the
hyperlink URL to come from cell C1. Then I am going to copy this cell down
the page because I have a whole series of text/screentip/hyperlinks that I
want to do this way.
Can I do this, and if so how?
Thanks
Cheers
Matt

 

[Frank Kabel]

Hi
do you mean
=HYPERLINK(C1,B1)

 

[Matt Jensen]

Thanks Frank
Not exactly...

I mean
=HYPERLINK(C1,A1)
except that I was also to hoping to specify the 'Screen Tip' (you'll see the
'Screen Tip' button in the top right of the 'Insert Hyperlink' dialogue box)
from another cell (b1 in this example).

Hmmm, not looking too hopeful

Thanks
Cheers
Matt

 

[Dick Kusleika]

Matt

To get the tooltip you want, I think you'll have to have a macro that
inserts a hyperlink instead of using the HYPERLINK worksheet function. Post
back if you want to explore that option.

 

[Matt Jensen]

Hey Dick
Yep that'd be great, a macro is fine. I've played with it but so far only
been able to set the hyperlink to link to another part of my worksheet. Any
info would be great.
Thanks
Cheers
Matt

 

[Dick Kusleika]

Matt

This puts hyperlinks in column D. You can run it as often as you like
because it deletes existing hyperlinks before it creates the new one.

Sub MakeHLs()

Dim rCell As Range
Dim rRng As Range

'The range to fill will start in D1 and end in column D as far down
'as there is data in column A
With ActiveSheet
Set rRng = .Range("D1", .Cells(.Rows.Count, 1).End(xlUp).Offset(0,
3))
End With

'Loop through all the cells
For Each rCell In rRng.Cells
'Delete any existing hyperlinks
Do While rCell.Hyperlinks.Count > 0
rCell.Hyperlinks(1).Delete
Loop
'Add a hyperlink
rCell.Hyperlinks.Add _
anchor:=rCell, _
Address:=rCell.Offset(0, -1).Value, _
ScreenTip:=rCell.Offset(0, -2).Value, _
TextToDisplay:=rCell.Offset(0, -3).Value
Next rCell

End Sub

 

[Matt Jensen]

Cheers mate - looks great
Thanks
Matt

Matt

This puts hyperlinks in column D. You can run it as often as you like
because it deletes existing hyperlinks before it creates the new one.

Sub MakeHLs()

Dim rCell As Range
Dim rRng As Range

'The range to fill will start in D1 and end in column D as far down
'as there is data in column A
With ActiveSheet
Set rRng = .Range("D1", .Cells(.Rows.Count, 1).End(xlUp).Offset(0,
3))
End With

'Loop through all the cells
For Each rCell In rRng.Cells
'Delete any existing hyperlinks
Do While rCell.Hyperlinks.Count > 0
rCell.Hyperlinks(1).Delete
Loop
'Add a hyperlink
rCell.Hyperlinks.Add _
anchor:=rCell, _
Address:=rCell.Offset(0, -1).Value, _
ScreenTip:=rCell.Offset(0, -2).Value, _
TextToDisplay:=rCell.Offset(0, -3).Value
Next rCell

End Sub

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com