Excel - Disable Auto Hyperlinking?

[jjefferson]

I have a column for users to enter email addresses to be used later by a VBA macro. As each entry is made, it is turned into a hyperlink to the email client.

I can disable/remove the hyperlink from these cells, but if a change is made or a new cell added the text is re-hyperlinked.

I'd like to turn off this "helpful" feature, but can't find the "switch" anywhere. It would be nice if it could be disabled only in the selected cells as it can be useful elsewhere.

My temporary workaround is to have the user split the address into two columns, and have the macro reassemble it into a valid address.

Thanks!
Jim

 

[xlhelp]

If the user is going through that much trouble to split the address, could you not get the user to press CTRL/z after entering the address? Please provide feedback of successes or failures

 

[smozgur]

I don't know if there is a switch but this way worked to disable auto hyperlink. Actually just a cheat what deletes hyperlinks if entered value is an hyperlink.

'Code goes to SheetX object module in VBA.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Hyperlinks.Count > 0 Then Target.Hyperlinks.Delete
End Sub

I hope this helps.

Regards
Oz

 

[jjefferson]

xlhelp - I'm not familiar with the CTRL/z you mention. Perhaps I'm doing it wrong, but when I type this key combination after entering the data, it just clears the cell contents.

oz - This sub does the trick. The only problem is that all the cell formatting is removed (color, bold, etc.). I can probably live with this unless you have an idea on what's happening?

Thanks for the help, folks.
Jim

 

[Dreamboat]

Hyperlinks are, by default, blue and underlined. If you still want that formatting, then you should be able to record a macro that does that and combine it with Oz's. Anne Troy

http://www.TheOfficeExperts.com

 

[jjefferson]

xlhelp - Sorry, I see what you were saying now. After they enter the address and it is hyperlinked, they can move back to that cell and press Ctrl-Z to remove the hyperlink. I wasn't leaving the cell first.

This works too, and the other cell properties aren't altered.

This will probably be acceptable to the user (if they can remember to do it). I think I will still tinker with Oz's routine to automatically remove the hyperlink, to have it retain the other cell properties.

Thanks again!
Jim

 

[jjefferson]

Thanks, Anne. Actually, I don't want to retain the blue and underlining. I want to keep the yellow background and bold font that were there before the change event fired.

The problem stemmed from them wanting to correct an address, and when they clicked on the cell the email client popped up.

Am I right in thinking that I will want to save the desired properties, do the target.hyperlink.delete method, and then restore the saved properties? Or am I making this too difficult.

Thanks,
Jim

 

[xlhelp]

If you are looking for a macro solution, please look at MS article Q233073

 

[jjefferson]

Thanks, I'll look at that. I ended up hard-coding my solution, and will continue the search for something more generic. Here's what I ended up with:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Hyperlinks.Count > 0 Then
        Target.Hyperlinks.Delete
        With Target.Interior
            .ColorIndex = 6
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
        Target.Font.Bold = True
    End If
End Sub

Thanks for everyone's help with this, and the effective shoves in fruitful directions.

Jim

 

[smozgur]

Wow,

I was away for a while and everything solved
Oz

http://www.TheOfficeExperts.com

 

[jjefferson]

Yeah, man! Snooze around here and someone else takes all the glory!

BTW, checked out the MS article xlhelp referenced; seems this little oversight has been corrected in Excel 2002. Time to get out that upgrade checkbook!

Thanks again,
Jim

 

[Dreamboat]

Yeah...and you know what else was *corrected*?

(They just can't seem to get it right!!)

Just when we're used to avoiding clicking on hyperlinks when we DON'T want to go there, and clicking on them when we do....we now have to Ctrl+click to go to a hyperlink... Anne Troy

http://www.TheOfficeExperts.com