hyperlink

How do I set up a column so that all new entries are
automatically hyperlinks?

http entries do that automatically now, but e-mail addresses
and word documents do not.

I have to manually convert it to a link for every new entry.
--
Thanks,
Brad.

Brad White wrote:
How do I set up a column so that all new entries are
automatically hyperlinks?


I think you would need a Worksheet_Change event procedure in the module
belonging to the worksheet. Something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim C As Range
Dim stAddr As String
' check if column A

If Intersect(Target, Me.Columns(1)) Is Nothing Then Exit Sub
For Each C In Intersect(Target, Me.Columns(1)).Cells
If C.Hyperlinks.Count = 1 Then C.Hyperlinks(1).Delete
If C.Text "" Then
If LCase(Left(C.Text, 5)) = "http:" Then
stAddr = C.Text
ElseIf InStr(C.Text, "@") 0 Then
If LCase(Left(C.Text, 7)) = "mailto:" Then
stAddr = C.Text
Else
stAddr = "mailto:" & C.Text
End If
Else
' don't know, just try the text we got
stAddr = C.Text
End If
C.Hyperlinks.Add C, Address:=stAddr, TextToDisplay:=C.Text
End If
Next
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill,

Thanks for the reply.

Turns out that what I wanted was slightly different from what I asked for.

I ended up with a macro attached to a shortcut.
If the cell is empty, pastes from the keyboard.
Then converts whatever is in the cell to a hyperlink.
Thanks for the jumpstart.

I wanted to pass the range into the sub, but this works.

Brad.
-----------------------
Here is what I did.

Private Sub InsertHyperLink()
Dim C As Range
Dim stAddr As String
Dim stDispl As String
'ByVal Target As Excel.Range

For Each C In Selection.Cells
If C.Hyperlinks.Count = 1 Then C.Hyperlinks(1).Delete
If C.Text = "" Then
C.PasteSpecial
End If
If C.Text "" Then
If LCase(Left(C.Text, 5)) = "http:" Then
stAddr = C.Text
stDispl = Right(C.Text, Len(C.Text) - 5)
ElseIf InStr(C.Text, "@") 0 Then
If LCase(Left(C.Text, 7)) = "mailto:" Then
stAddr = C.Text
stDispl = Right(C.Text, Len(C.Text) - 7)
Else
stAddr = "mailto:" & C.Text
stDispl = C.Text
End If
Else
' don't know, just try the text we got
stAddr = C.Text
stDispl = C.Text
End If
C.Hyperlinks.Add C, Address:=stAddr, TextToDisplay:=stDispl
End If
Next
End Sub

Sub Hyperlink()
'
' Hyperlink Macro
' Macro recorded 11/3/2004 by bwhite
'
' Keyboard Shortcut: Ctrl+k
Call InsertHyperLink

End Sub