Excel VBA - disable hyperlinks on specific ranges

What's the VBA code to disable hyperlinks from specific ranges?  These ranges have email addresses and Excel is automatically creating an email hyperlink.

Thanks, --Andres
AndresHernandoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
what do you mean disable ?? Does excel create the email once they are created or once the cell containing the hyperlink is clicked ?
gowlfow
0
gowflowCommented:
Here it is
ActiveCell.Hyperlinks.Delete
Will remove the hyperlink from the active cell (you can use a range)

ActiveCell.Hyperlinks.Add ActiveCell, ActiveCell.Value
Will add Hyperlink to the active cell you can also use a range

gowflow
0
gowflowCommented:
in the preceeding examples if you need it to be a range then it would look like this

Range("A1:A100").Hyperlinks.Delete
will delete all hyperlinks in the range A1 to A100
----------------------
Dim Cel
For Each Cel In Range("A1:A100")
Cel.Hyperlinks.Add Cel, Cel.Value
Next Cel
-----------------------
Will re-instate Hyperlinks in the Range A1 to A100

gowflow
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

gowflowCommented:
Did you have a chance to check the proposed answer ?
gowflow
0
AndresHernandoAuthor Commented:
Hi goflow, sorry for the delay...  I was finally able to get back to this.
Your solution would work but I can't use it because it also deletes the range's formatting!
What I really need to do is to stop Excel from creating an email when the range is clicked.  Any ideas?

Thanks, --Andres
0
AndresHernandoAuthor Commented:
goflow, I figured a way to make it work.  After your code I simply add formatting code.  Works fine.

Thanks!  --Andres
0
gowflowCommented:
Well good for you I had anyway fixed it this way. below are 2 subs that will only remove the hyperlink when there is one keeping formating intact. You may add buttons to each one individually so you can activate hyperlink and de-activate it when you need. pls chk the attached file it even keeps formating in the hyperlinks.

TKs for the grade.
gowflow
Sub DELHyperlinks()
Dim Cel As Range
Dim Rng As Range
Dim RngTmp As Range

'Delete Hyperlink in the specified range
Application.EnableEvents = False
Set Rng = Range("A1:A100")

For Each Cel In Range(Rng.Address)
If InStr(1, Cel.Value, "@") > 0 Then
    Cel.Copy Range("IV1")
    Cel.Hyperlinks.Delete
    Range("IV1").Copy
    Cel.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
    Cel.PasteSpecial Paste:=xlPasteFormats
    Range("IV1").Delete
    Application.CutCopyMode = False
End If
Next Cel

Application.EnableEvents = True

End Sub

Sub ADDHyperlinks()
Dim Cel As Range
Dim Rng As Range

Application.EnableEvents = False
Set Rng = Range("A1:A100")
'Re-instate Hyperlinks in the specified range
For Each Cel In Range(Rng.Address)

If InStr(1, Cel.Value, "@") > 0 Then
    Cel.Copy Range("IV1")
    Cel.Hyperlinks.Add Cel, Cel.Value
    Range("IV1").Copy
    Cel.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
    Cel.PasteSpecial Paste:=xlPasteFormats
    Range("IV1").Delete
    Application.CutCopyMode = False
End If
Next Cel

Application.EnableEvents = True

End Sub

Open in new window

Hyperlinks.xls
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.