Multiple Apps

Remove All Hyperlinks From Selection

Ease of Use

Easy

Version tested with

2000 

Submitted by:

MWE

Description:

Removes all hyperlinks from the selection. Displayed text is unchanged 

Discussion:

There are many reasons why a worksheet or other object could have hyperlinks no longer wanted. One example is Excel's irritating habit of generating mailto hyperlinks when you type an email address into a cell. If you keep, say, account information in a spreadsheet, there may be an associated email address. Leaving the hyperlinks in place is not dangerous, but it is irritating when you mistakenly click on one of these cells and have your email tool start up. Manual removal on a case-by-case basis is not hard, but it is tedious. 

Code:

instructions for use

			

Option Explicit Sub DelSelHyperLinks() '**************************************************************************************** ' ' Target Application: most MS appls ' Function: deletes all hyperlinks in the current selection ' '**************************************************************************************** ' Dim I As Integer, NumLinks As Integer, NumDeleted As Integer Dim Rtn As VbMsgBoxResult NumLinks = Selection.Hyperlinks.Count If NumLinks < 1 Then MsgBox "No hyperlinks found in the current selection.", vbInformation Exit Sub End If Rtn = MsgBox("This procedure will remove all hyperlinks in current selection:" + Chr(10) + Chr(10) + _ "# hyperlinks to be deleted " + Str(NumLinks) + Chr(10) + Chr(10) + _ "OK ?", vbQuestion + vbYesNo) If Rtn <> vbYes Then Exit Sub NumDeleted = 0 For I = NumLinks To 1 Step -1 Selection.Hyperlinks(I).Delete NumDeleted = NumDeleted + 1 Next I MsgBox Str(NumDeleted) + " hyperlinks removed", vbInformation End Sub

How to use:

  1. Copy the above code.
  2. Open a workbook or Word doc.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. From the Insert Menu, choose Insert | Module.
  5. Paste the code into the right-hand code window.
  6. Click the Save button and close the VBE window.
 

Test the code:

  1. Select a range of cells or some document text (as applicable)
  2. Go to Tools | Macro | Macros and double-click DelSelHyperlinks
 

Sample File:

DelSelHyperLink.zip 8.63KB 

Approved by mdmackillop


This entry has been viewed 67 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express