Hi,

I use the following sub to delete the mail to hyperlinks that appear
for any email addresses entered in the sheet.

Public Sub RemoveHyperlinks(rng As Range)
Dim h As Hyperlink
For Each h In rng.Hyperlinks
h.Delete
Next h
End Sub

When the sheet is not protected it executes fine.
When the sheet is protected and the cells are locked the method fails
as expected.
But if the sheet is protected and the cells are unlocked then the
method does not remove the hyperlinks. In fact when I went into debug
mode and watched 'rng', it reported rng with Hyperlinks.Count=0 .

After this method the sheets hyperlink part is in a strange state. It
does not create hyperlinks anymore (yippee), even when you remove the
protection. But I am guessing this is an invalid state and could mess
up the rest of the execution.

Any explanations?

Thanks and regards,
Jayant

if you dont want excel to convert (typed) text to url/hyperlinks
simply turn of autocorrect options.

Tools/AC options
tab:AF as you type

DEselect
Replace as you type:
internet and networkpaths with hyperlinks

jippee indeed.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


jjk wrote :

Hi,

I use the following sub to delete the mail to hyperlinks that appear
for any email addresses entered in the sheet.

Public Sub RemoveHyperlinks(rng As Range)
Dim h As Hyperlink
For Each h In rng.Hyperlinks
h.Delete
Next h
End Sub

When the sheet is not protected it executes fine.
When the sheet is protected and the cells are locked the method fails
as expected.
But if the sheet is protected and the cells are unlocked then the
method does not remove the hyperlinks. In fact when I went into debug
mode and watched 'rng', it reported rng with Hyperlinks.Count=0 .

After this method the sheets hyperlink part is in a strange state. It
does not create hyperlinks anymore (yippee), even when you remove the
protection. But I am guessing this is an invalid state and could mess
up the rest of the execution.

Any explanations?

Thanks and regards,
Jayant

Hi keepITCool,

Thanks for your response. However, The option to turn off the
autocorrect is not available in older versions of Excel. This is the
reason for the VBA code.
Were you able to duplicate the problem?

Thanks and regards,
Jayant

afaik
xl97 doesnt autocorrect.
xl2k ????? (dont have cant check)
xlXP/xl2003 have the option to (de)select

will check into replication ltr.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


jjk wrote :

Hi keepITCool,

Thanks for your response. However, The option to turn off the
autocorrect is not available in older versions of Excel. This is the
reason for the VBA code.
Were you able to duplicate the problem?

Thanks and regards,
Jayant

Thanks for your effort.
xl2k doesnt have the option either.
For once excel is throwing a bug that is helping me. ( at least for now
;)

Regards,
Jayant

keepITsimple..

Sub RemoveHyperlinks(rng As Range)
rng.Hyperlinks.Delete
End Sub

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


jjk wrote :

Thanks for your effort.
xl2k doesnt have the option either.
For once excel is throwing a bug that is helping me. ( at least for
now ;)

Regards,
Jayant