Possible to force automatic hyperlink generation in a cell?

  • Thread starter Thread starter Android
  • Start date Start date
A

Android

Hi,

I have about 500 cells with destimations on network drives, such as

\\fileshare\public\Projects\Project Name\....\contactus.htm

I want to change these to automatic hyperlink. The only way I can think of
is to edit each cell one by one and add RETURN at the end of each line.

Is there a way to automate this. E.g., is there a way to search and replace
each ".htm" with ".htm + Carriage return"?

Regards,

Android.
 
Hi
try the following code snippet for processing your selection:
Sub MakeHyperlinks()
Dim cell As Range
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
With Worksheets(1)
.Hyperlinks.Add Anchor:=cell, _
Address:=cell.Value, _
ScreenTip:=cell.Value, _
TextToDisplay:=cell.Value
End With
Next cell
End Sub

for more see
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
(at the bottom of this page)
 
Hi Frank,

This may be a bit naive. I keep getting error 424 (Object required).

I don't follow what SpecialCells(xlConstants, xlTextValues) is doing, or
what to use for xlConstants, xlTextValues. So I tried a simple version below
(where Rgx is a named range ='Web Content Management Matrix'!$G$30:$G$31).
This gives the same error (424). What am I missing?

Private Sub MakeHyperlinksToRange_Click()
Dim cell As Range
Dim Rgx As Range

For Each cell In Rgx
With Worksheets(1)
.Hyperlinks.Add Anchor:=cell, _
Address:=cell.Value, _
ScreenTip:=cell.Value, _
TextToDisplay:=cell.Value
End With
Next cell
End Sub
 
Back
Top