If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Automatic hyperlinks in Excel 97
Hello all,
Excel 97 doesn't have the option of changing text to hyperlinks on typing, as later versions do. To ensure text entered in a certain range is returned in hyperlink form, I came up with a VBA solution, but I'm still wondering if there is a better solution. Code follows below. I got my inspiration for this workaround from some D. McRitchie code (http://www.mvps.org/dmcritchie/excel...MakeHyperLinks). If anyone has a better suggestion, or I've missed an option Excel97 has, please tell me. The sheet in question has to work on Excel97, 2k and 2k3 workstations. Thanks in advance. Eric '==========StartOfCode========== Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Dim TheAdress As String, TheText As String If Intersect(Target, Range("c4:c14")) Is Nothing Then Exit Sub Else TheAdress = Trim(Target) TheText = Trim(Target) 'Prevent loop. If Left(Target, 10) = "=HYPERLINK" Then Exit Sub 'Cell mustn't be empty. If TheText = "" Then Exit Sub 'There has to be a '.' in the text. If InStr(1, TheAdress , ".") = 0 Then Exit Sub 'There has to be an '@' in the text. If InStr(1, TheAdress , "@") = 0 Then Exit Sub If LCase(Left(TheAdress , 7)) "mailto:" Then TheAdress = "mailto:" & TheAdress End If Target.Formula = "=HYPERLINK(""" & TheAdress & _ """,""" & TheText & """)" End If End Sub '==========EndOfCode========== |
Thread Tools | |
Display Modes | |
|
|