CODEWITHSUNDEEP
excelexcel-2013
Andrew Perry
Andrew Perry

Reputation: 765

How do I stop email addresses from being parsed as hyperlinks?

I've got a list of ships in a spreadsheet, and one of the columns will contain the email address for each ship. However, entering an email address turns it into a hyperlink, while I'd like it all stored as plain text. Is there any way to either prevent that from happening or strip the hyperlink out afterwards?

My current attempt is this little sub:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("VesselEmails")) Is Nothing Then Exit Sub
Application.EnableEvents = False
    If Target.Hyperlinks.Count > 0 Then Target.Hyperlinks.Delete
Application.EnableEvents = True
End Sub

It doesn't do the job, unfortunately. Is there any way I can do to make it work, or a format mask I can apply or something?

EDIT: To clarify, I don't want to switch off email address autoformatting altogether, just in that column.

Upvotes: 1

Views: 140

Answers (3)

teylyn
teylyn

Reputation: 35935

I realize you want to apply this to only one column and this answer does not achieve that. But for the record, here is how to prevent typed text turning into links or email addresses in all workbooks you open with Excel:

File > Options > Proofing > AutoCorrect options > Autoformat as you type > untick Internet and network paths with hyperlinks

enter image description here

Upvotes: 1

Robin Mackenzie
Robin Mackenzie

Reputation: 19289

You can try this as an alternative - it is working for me in Excel 2010:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Column = 5 Then
        Application.AutoFormatAsYouTypeReplaceHyperlinks = False
    Else
        Application.AutoFormatAsYouTypeReplaceHyperlinks = True
    End If

End Sub

This makes the behaviour column-specific per your edit to the question. Obviously you can extend the If Target.Column = 5 code to include more columns depending on your form.

Upvotes: 1

Dawid SA Tokyo
Dawid SA Tokyo

Reputation: 376

You can remove hyperlinks in a column with Columns(1).Hyperlinks.Delete. Run this line in your on change sub and change the 1 in Columns(1) to the column number that has the email data - you can change it to a column letter as well, for example Columns("E").
Hope that will help.

Upvotes: 2

Related Questions