Issues with Hyperlinks in XL97
 

My program includes a form that allows user to enter email and website, but
with some users (not all) the text becomes a hyperlink. It shouldn't. My
program allows XL97-XP so I want to program for XL97. However, when I use
the following in XL97

Sub ZapHyperlink()
ActiveSheet.Hyperlinks.Delete
End Sub

it reformats the font and borders in cell. It shouldn't. I can include
lengthy reformatting procedures to sub, but prefer not to. Does anyone have
a suggestion how to cleanly deal with this?

Thank you in advance. Jim Kobzeff

Issues with Hyperlinks in XL97
 

You could try snatching the text, clearing the range, then re-assigning
the text with a single quote ' in front of it:

Dim ws As Worksheet
Dim hyp As Hyperlink
Dim txt As String
Dim sRng As String

Set ws = Application.ActiveSheet

For Each hyp In ws.Hyperlinks
sRng = hyp.Range.Address
txt = hyp.Range.Text
ws.Range(sRng).Clear
ws.Range(sRng).Value = "'" & txt
Next
Set ws = Nothing

It worked for me, but I'm using Excel XP.

----
Nick Hebb
BreezeTree Software
http://www.breezetree.com

Issues with Hyperlinks in XL97
 

Hi,

how about something like this:
(I tested in Excel 2000. the font properties that are from the
'Hyperlink' style may be lost.)

Sub DeleteHyperlink()
ExecuteExcel4Macro "DEFINE.STYLE(""Normal"",0,0,0,0,0,0)"
ActiveSheet.Hyperlinks.Delete
ExecuteExcel4Macro "DEFINE.STYLE(""Normal"",1,1,1,1,1,1)"
End Sub

--
HTH,

okaizawa

JK wrote:
My program includes a form that allows user to enter email and website, but
with some users (not all) the text becomes a hyperlink. It shouldn't. My
program allows XL97-XP so I want to program for XL97. However, when I use
the following in XL97

Sub ZapHyperlink()
ActiveSheet.Hyperlinks.Delete
End Sub

it reformats the font and borders in cell. It shouldn't. I can include
lengthy reformatting procedures to sub, but prefer not to. Does anyone have
a suggestion how to cleanly deal with this?

Thank you in advance. Jim Kobzeff