Remove hyperlinks

[v_gyku]

I am trying to remove hyperlink from excel sheet.
I am able to remove hyperlink from auto shapes like callouts, arrow...
its working for images also.
only hyperlinks from organization chart is not goin....

if u see code given below... its not counting the hyperlinks in the organization chart.. i.e. they are not deleted...


MsgBox (wSheet.Hyperlinks.Count)
For i = wSheet.Hyperlinks.Count To 1 Step -1
If cancelPwd = True Then
Exit For
End If
If wSheet.Hyperlinks(i).Type <> 0 Then
wSheet.Hyperlinks(i).Delete
ObjExcel.Save
End If
Next i

[malik641]

Hey v_gyku,
Just so you know, I placed your code in VB tags :thumb

[TonyJollans]

Hi v_gyku,

Organization Charts are embedded objects and their properties do not belong to the sheet.

You need to look at the Hyperlink property of the individual Shape.Diagram.Nodes (Org Charts are Diagrams of Type msoDiagramOrgChart) and you'll need to trap some errors as well I think - if there isn't a hyperlink, the property returns an error and it isn't a collection, so no count.

[Justinlabenne]

Cross-Posted Here (http://www.vbforums.com/showthread.php?t=361378)

[v_gyku]

I dont want to remove text hyperlinks :

can u just modify this code?



Dim nolinks As Integer
nolinks = 0
Dim Shp As Excel.ShapeRange
Dim IShp As Excel.Shape
Dim i As Integer
Dim j As Integer
On Error GoTo ResNextShp
If wSheet.Hyperlinks.Count = 0 Then
nolinks = nolinks + 1
Else
For j = 1 To AppExcel.ActiveSheet.Shapes.Count
AppExcel.ActiveSheet.Shapes(j).Select
Set Shp = Selection.ShapeRange
If Shp.HasDiagramNode = msoTrue Then
For i = 1 To Shp.DiagramNode.Diagram.Nodes.Count
Set IShp = Shp.DiagramNode.Diagram.Nodes(i).Shape
If IShp.Hyperlink.Address <> "" Then
IShp.Hyperlink.Delete
hashyperlinks = True
End If
Next i
End If
Set IShp = AppExcel.ActiveSheet.Shapes(j)
If IShp.Hyperlink.Address <> "" Then
IShp.Hyperlink.Delete
hashyperlinks = True
'End If
End If
Next j
i = 0
For i = AppExcel.ActiveSheet.Hyperlinks.Count To 1 Step -1
AppExcel.ActiveSheet.Hyperlinks(i).Delete
hashyperlinks = True
Next i
End If
ResNextShp:
Resume Next

[Killian]

I don't understand.... you have already posted the code to do this on two forums. If you take out the parts that aren't required, it removes the hyperlinks from diagrams !!!???


Dim Shp As Excel.ShapeRange
Dim IShp As Excel.Shape
Dim j As Integer
Dim i As Integer
On Error Resume Next
For j = 1 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(j).Select
Set Shp = Selection.ShapeRange
If Shp.HasDiagramNode = msoTrue Then
For i = 1 To Shp.DiagramNode.Diagram.Nodes.Count
Set IShp = Shp.DiagramNode.Diagram.Nodes(i).Shape
If IShp.Hyperlink.Address <> "" Then
IShp.Hyperlink.Delete
End If
Next i
End If
Next j