remove macros from shapes

[vbajunkie]

Hi all,

I have several workbooks that each have about 20 sheets, and each sheet has dozens of shapes (text boxes, command buttons, ovals, etc.) and each shape has a macro assigned to it. What I want to do is disable the OnAction property from each shape with one macro.

I have been able to do them individually by selecting each shape and setting OnAction = "", but when I try to do set this up for an entire worksheet or workbook I'm getting the message "Unable to set the OnAction property of the DrawingObjects class." Below is the code that I've tried.

This sub works if I do them one by one:

Sub DisableShapes1 ()
ActiveSheet.Shapes("Text Box 1").Select
Selection.OnAction = ""
Range("A1").Select
End Sub

But this sub gives me the error:

Sub DisableShapes2 ()
ActiveSheet.Shapes.SelectAll
Selection.OnAction = ""
Range("A1").Select
End Sub

If I run the 2nd Sub on a sheet that does not have any command buttons it seems to works fine. How do I go about getting this to work? Would I need to instead try to loop through each shape?

Any help appreciated...

vbajunkie

 

[erik.van.geit]

Hi,

Does this work for you?
Brief testing worked for me.

Sub test()
Dim shp As Shape
Dim sht As Worksheet

    For Each sht In Worksheets
        For Each shp In sht.Shapes
        shp.OnAction = ""
        Next shp
    Next sht
End Sub

kind regards,
Erik

 

[vbajunkie]

thanks Erik, but it did not work. It's halting on the following line:

shp.OnAction = ""

and throwing the following error...

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

It seems to only want to work on shapes that are not command buttons. It did work on a sheet that only had basic shapes like text boxes. Why would only the command buttons cause this?

Thanks

vbajunkie

 

[erik.van.geit]

It worked for me for commandbuttons created from FORMS toolbar.

Commandbuttons (or any shapes) created from CONTROLS toolbar do NOT have an "onaction" property: their "event"code is located in the sheetmodule: you can eighter delete that code or the shapes (I could help with that)
On top (first line after Sub test() ) of your code you can put: "on error resume next".

 

[vbajunkie]

AHA!

These are command buttons from the Controls toolbar. Is there a way to disable the macros in these buttons programatically without doing them one by one?

Thanks Erik!

 

[vbajunkie]

I like the idea of just deleting the code behind the button. It will have all been exported before then anyway. I do not want the shapes deleted as I may want to reuse them in the future.

Thanks....

vbajunkie

 

[erik.van.geit]

you can delete all sheetcode programatically, but if it is not too much work manually, you can go that route

http://www.cpearson.com/Excel/vbe.aspx