?
Solved

Excel VBA - disable hyperlinks on specific ranges

Posted on 2011-10-15
7
Medium Priority
?
610 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:AndresHernando
  • 5
  • 2
7 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 36973231
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36973264
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
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 36973284
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:gowflow
ID: 36975655
Did you have a chance to check the proposed answer ?
gowflow
0
 

Author Comment

by:AndresHernando
ID: 36976309
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
 

Author Comment

by:AndresHernando
ID: 36977804
goflow, I figured a way to make it work.  After your code I simply add formatting code.  Works fine.

Thanks!  --Andres
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36978340
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question