How can I get a button to fully execute a Macro in Excel?

Question

When I execute the following macro without a button, The shapes will delete beautifully. When I execute the macro with a button, the shapes are selected, but they are not deleted. I don't understand why. Is there something wrong with the code? Thanks in advance for your help!

Sub DeleteAllObjects()
    Dim ws As Worksheet
    Set ws = Worksheets("Flowchart")

    ws.Shapes.SelectAll
    Selection.Delete
 End Sub

I would right click your button and verify that you have it set to the correct macro. I've had massive headaches because the button is assigned to a macro that was in a different version of the same workbook ;) — Blake Turner, Aug 16 '18 at 16:12
This code appears to only delete the shapes if ws is active. — David Zemens, Aug 16 '18 at 16:13
You could also try to optimize your code a bit more. Something like: Worksheets("Flowchart").shapes.delete — Blake Turner, Aug 16 '18 at 16:13
Thank you, Blake! When a code doesn't execute like I want it, I usually assume that I made an error and recheck it. Fortunately, the button was assigned to the correct macro. Also, I used the Macro recorder to start writing the code and noticed that it specified the deletion from the "Active Sheet". So I rewrote the code, thinking that I was outsmarting the recorder. Lesson learned! Thank you! — CPILon, Aug 16 '18 at 17:57

score 1 · Answer 1 · answered Aug 16 '18 at 16:17

I used an iterative approach because my observation was that your method did not work if the ws was not Active at runtime (and to avoid the spaghetti-code of needlessly activating objects).

Your problem is because the Excel Selection only exists relative to the ActiveSheet, so when you do Selection.Delete you're actually deleting something on the ActiveSheet, which isn't the same as the ws!

I have tested this and it works when called from button (on another sheet) or when invoked manually from F5 in the IDE.

 Sub DeleteAllObjects()

 Dim ws As Worksheet
 Dim s As Long

 Set ws = Worksheets(3)
 For s = ws.Shapes.Count To 1 Step -1
    ws.Shapes(s).Delete
 Next

 End Sub

@CPILon cheers, please consider voting up, or Accepting the answer if it solves the problem. https://stackoverflow.com/help/someone-answers — David Zemens, Aug 16 '18 at 17:57

How can I get a button to fully execute a Macro in Excel?

1 Answers1