PDA

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 :)