View Full Version : Solved: Removing hyperlinks
Tenspeed39355
06-13-2006, 05:35 AM
Good morning. I have a problem with removing hyperlinks from a ss
There are times that one or two columns will have hyperlinks in them. When I run the mouse across the ss the hand will come up and then the white cross will come up on another column. Is there a way to remove the hyperlinks from the problem columns? Thanks for your time with this.
Max
johnske
06-13-2006, 06:30 AM
Hi Max,
Cells.Hyperlinks.Delete for the whole w/sheet or Selection.Hyperlinks.Delete... etc :)
snicho
06-14-2006, 02:39 AM
(This is my first posting, so I apologise in advance if there are formatting problems.)
Not sure if you want to go to this extent, however, something along these lines provides you a little more flexibility:
Public Sub DeleteBrokenHyperlinks()
Dim test As Boolean
Dim count As Integer
count = 0
test = True
On Error GoTo OnError
For Each h In ActiveSheet.Hyperlinks
If Dir(h.Address) = "" Then
MsgBox h.Name & vbCr & _
h.Address & vbCr & _
h.SubAddress & vbCr & _
"in cell " & h.Range.Address & vbCr & _
"link path does not exist"
test = False
response = MsgBox("Do you want to delete this link?", vbYesNo)
If response = vbYes Then
Range(h.Range.Address).Hyperlinks(1).Delete
Application.StatusBar = False
Exit Sub
End If
End If
count = count + 1
Application.StatusBar = "Links checked: " & count
Next
If test Then MsgBox "All Hyperlinks test ok"
Application.StatusBar = False
Exit Sub
OnError:
MsgBox ("Error with: " & h.Name & vbCr & "File not found.")
Application.StatusBar = False
End Sub
Tenspeed39355
06-14-2006, 04:10 AM
To JOHNSKE I hate to do this but I do not understand your reply.
As to cells,hyperlink where do you want me to start. Once I find the starting point I think I can run with the rest.
Max
johnske
06-14-2006, 04:25 AM
To JOHNSKE I hate to do this but I do not understand your reply.
As to cells,hyperlink where do you want me to start. Once I find the starting point I think I can run with the rest.
MaxHi Max,
Here's some examplesOption Explicit
Sub RemoveSheetLinks()
' 'to remove all hyperlinks from a sheet
Cells.Hyperlinks.Delete
End Sub
Sub RemoveSelectionLinks()
' 'to remove all hyperlinks from a selection
Selection.Hyperlinks.Delete
End Sub
Sub RemoveColumnLinks()
' 'to remove all hyperlinks from a column
Columns(1).Hyperlinks.Delete
End Sub
Sub RemoveRowLinks()
' 'to remove all hyperlinks from a row
Rows(1).Hyperlinks.Delete
End Sub
Sub RemoveRangeLinks()
' 'to remove all hyperlinks from a given range
Range("A1:Z30").Hyperlinks.Delete
End Sub
Tenspeed39355
06-16-2006, 05:32 AM
Johnske The examples you gave me on removing the hyperlinks works like
a charm. Thank you very much. You guys are great in helping with problems. Keep up the good work.
Max:clap: I will give ratings.
johnske
06-16-2006, 04:50 PM
Not a prob Max - thanx :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.