Mass removal of macro assignments from shapes 1

[JTBorton]

I inherited a project from another person and I need to modify it. There are a LOT (as in a LOT LOT LOT) of shapes on his excel spreadsheets that are linked to macros and I want to remove all of the macro links so I can manipulate the shapes. I tried the following loop but it crashed and burned at the highlighted line of code and gave the following error:

Run-time error '438':
Object doesn't support this property or method

Public Sub UnAssignAMacro()
Dim wkSht As Excel.Worksheet, objShape As Excel.Shape
    For Each wkSht In Worksheets
        [highlight]For Each objShape In wkSht[/highlight]
            ActiveSheet.Shapes(objShape).OnAction = ""
        Next objShape
    Next wkSht
End Sub

Any ideas on how to do it?

-JTBorton
Another Day, Another Disaster

 

[PHV]

I'd try this instead:

For Each wkSht In Worksheets
  For Each objShape In wkSht.Shapes
    objShape.OnAction = ""
  Next objShape
Next wkSht

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

Thanks PHV. I ran your code and it got about half way through and crashed. I added some things to the code to monitor its progress, as follows:

Public Sub UnAssignAMacro()
Dim intObject As Integer, V As Integer, wkRecords As Excel.Worksheet
Dim wkSht As Excel.Worksheet, objShape As Excel.Shape

Set wkRecords = Worksheets("Sheet1")
    
    intObject = 0
    For Each wkSht In Worksheets
        intObject = 0
        For Each objShape In wkSht.Shapes
            V = V + 1
            intObject = intObject + 1
            wkRecords.Cells(V, 1) = wkSht.Name
            wkRecords.Cells(V, 2) = intObject
            wkRecords.Cells(V, 3) = objShape.Name
            objShape.OnAction = ""
        Next objShape
    Next wkSht
    
Set wkRecords = Nothing
Set wkSht = Nothing
Set objShape = Nothing

End Sub

It's getting to the fourth sheet and crashing when it hits the drop down list, which is a data validation list that I added with it's source data on the sheet off the screen. At least I think that's what this drop down list is. I don't know of any others.

Run-time error '1004':
Application-defined or object-defined error

wkSht.Name # objShape.Name
-------------- -- -----------------
K-Line HECO 57 Rectangle 278
K-Line HECO 58 Rectangle 280
K-Line HECO 59 Line 281
[highlight]K-Line HECO 60 Drop Down 282[/highlight]

Is there any way around this without deleted my validation lists?

-JTBorton
Another Day, Another Disaster

 

[PHV]

Brute force method:

On Error Resume Next
objShape.OnAction = ""
On Error Resume 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

Thanks PHV. There were a lot of layered groups so I had to run the code several times just ungrouping the layers, then I was able to unassign the macros.

-JTBorton
Another Day, Another Disaster