remove macros from shapes

V

vbajunkie

New Member
Joined
Jan 31, 2006
Messages
4
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Hi,

Does this work for you?
Brief testing worked for me.
Code: 
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
 
Upvote 0
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
 
Upvote 0
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".
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Disable shapes when Workbook is protected VBA
2
Replies
12
Views
427
mumps
mumps
B
  • Question
Event code to give message box and change color of auto-shape
Replies
4
Views
157
bh24524
B
L
  • Solved
VBA to resize shape incrementally
Replies
3
Views
198
Lil Stinker
L
I
  • Question
Using shapes as opposed CommandButtons
Replies
6
Views
132
Tupe77
T
K
  • Question
10 Buttons (Shapes) only first 9 work. #10 does not.
Replies
6
Views
224
KarateKidClone
K

Forum statistics

Threads
1,214,945
Messages
6,122,395
Members
449,081
Latest member
JAMES KECULAH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top