VBA - format a range of cells as hyperlink?
 

Hi

I have a spreadsheet which contains the locations of some files starting at
C2 and going down as far as required. What I would like to do is put some
VBA code that either turns the text in C2:C? to a hyperlink to the file
(preferred) or put a hyperlink to the file in D2:D?. I've tried but as yet
with no success.

Can anyone help?

Thanks
SteveB

VBA - format a range of cells as hyperlink?
 

"Steveb" skrev i en meddelelse
...
Hi

I have a spreadsheet which contains the locations of some files starting
at C2 and going down as far as required. What I would like to do is put
some VBA code that either turns the text in C2:C? to a hyperlink to the
file (preferred) or put a hyperlink to the file in D2:D?. I've tried but
as yet with no success.

Can anyone help?

Thanks
SteveB

Hi Steve

Here's one way to accomplish it:

Sub ToHyperlink()
'Leo Heuser, 29-7-2006
Dim Cell As Range
Dim CheckRange As Range

On Error Resume Next

Set CheckRange = Sheets("Sheet1").Range("C2")
Set CheckRange = Range(CheckRange, Cells(ActiveSheet.Rows.Count, _
CheckRange.Column).End(xlUp))

For Each Cell In CheckRange.Cells
Cell.Parent.Hyperlinks.Add Anchor:=Cell, Address:= _
Cell.Value, TextToDisplay:=Cell.Value
Next Cell

On Error GoTo 0

End Sub

--
Best regards
Leo Heuser

Followup to newsgroup only please.

VBA - format a range of cells as hyperlink?
 

"Leo Heuser" wrote in message
...
"Steveb" skrev i en meddelelse
...
Hi

I have a spreadsheet which contains the locations of some files starting
at C2 and going down as far as required. What I would like to do is put
some VBA code that either turns the text in C2:C? to a hyperlink to the
file (preferred) or put a hyperlink to the file in D2:D?. I've tried but
as yet with no success.

Can anyone help?

Thanks
SteveB

Hi Steve

Here's one way to accomplish it:

Sub ToHyperlink()
'Leo Heuser, 29-7-2006
Dim Cell As Range
Dim CheckRange As Range

On Error Resume Next

Set CheckRange = Sheets("Sheet1").Range("C2")
Set CheckRange = Range(CheckRange, Cells(ActiveSheet.Rows.Count, _
CheckRange.Column).End(xlUp))

For Each Cell In CheckRange.Cells
Cell.Parent.Hyperlinks.Add Anchor:=Cell, Address:= _
Cell.Value, TextToDisplay:=Cell.Value
Next Cell

On Error GoTo 0

End Sub

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Thanks Leo, works perfectly.

SteveB

VBA - format a range of cells as hyperlink?
 

"Steveb" skrev i en meddelelse
...

"Leo Heuser" wrote in message
...
"Steveb" skrev i en meddelelse
...
Hi

I have a spreadsheet which contains the locations of some files starting
at C2 and going down as far as required. What I would like to do is put
some VBA code that either turns the text in C2:C? to a hyperlink to the
file (preferred) or put a hyperlink to the file in D2:D?. I've tried but
as yet with no success.

Can anyone help?

Thanks
SteveB

Hi Steve

Here's one way to accomplish it:

Sub ToHyperlink()
'Leo Heuser, 29-7-2006
Dim Cell As Range
Dim CheckRange As Range

On Error Resume Next

Set CheckRange = Sheets("Sheet1").Range("C2")
Set CheckRange = Range(CheckRange, Cells(ActiveSheet.Rows.Count, _
CheckRange.Column).End(xlUp))

For Each Cell In CheckRange.Cells
Cell.Parent.Hyperlinks.Add Anchor:=Cell, Address:= _
Cell.Value, TextToDisplay:=Cell.Value
Next Cell

On Error GoTo 0

End Sub

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Thanks Leo, works perfectly.

SteveB


You're welcome, Steve, and thanks for the feedback :-)

Leo Heuser