Running Excel 4.0 macro using button

Question

Macros on my spreadsheet that have been working for years, stopped working via buttons, though they still work from the VBA Developer window.

I use a normal shape (rectangle) as the button and assigned the macro to it (selecting from "ThisWorkbook").

To head off suggested fixes I've seen for other similar posts:

• It is still an .xlsm file
• Macros are still enabled.
• It's running on the same laptop as before (Windows 10, Office 365).
• No Windows updates have occurred lately.
• I only have this one file open.
• I tried rebooting laptop and restarting Excel.
• I'm not using an Active-X Control.
• I tried it with a Form Control button and a regular Shape button - neither work.
• It's not related to the actual VBA code (see below for proof).

I created a new program to show the problem is not the code itself:

Sub button_not_working()
    MsgBox "button_not_working"
End Sub

This program works using the green Play button in the VBA screen, but not via an assigned button on a sheet.

Code is in a normal VBA code Module (not "ThisWorkbook" area on VBA screen).

Macro is assigned by right-clicking shape, and the list of available Macros is just those in "This Workbook" on the Assign Macro popup.

When clicking the Shape to run the assigned macro I get this error message.

Answer 1

You can't call code from ThisWorkbook in a button event.

I like to put the button events in the code behind the sheet where the button lives. This way the code move with the sheet wherever that sheet is copied.

Use a form button. Right-click on the form button and select Assign Macro.... Then select the VBA subroutine from the list that pops up. Only procedures visible on this popup will work.

NOTE: Never use _ in any names in VBA. It's reserved for event handling.

Public Sub ButtonIsWorking()
    MsgBox "button is working"
End Sub

Code in sheet:

Assign Macro:

Button press:

Answer 2

Often this issue can occur due to multiple screens or resolution difference issues. If you are using a laptop connected to screens try using the button on the laptop while it's disconnected from your monitors. If this fixes the issue then ensure you have the same resolution and/or scaling between the two setups.

If this change only happened recently and is on a desktop or laptop screen without additional monitors ensure your scaling is set to 100% in case it has recently changed.

Answer 3

Something to try: Make sure your 'caller' shapes have unique names
Note: It's possible to have multiple shapes with the same name and (for whatever reason) that can confuse excel's shape-to-macro-connection.

Additional Info 1:
If you use a 'grouped shapes' object as a control, you should assign the same name to all shapes in the group.

Additional Info 2:
The original OP symptom was simply "Macros ... stopped working via buttons." The OP has since been updated, and shows an error message. The disconnect-due-to-non-unique-naming described here doesn't elicit an error message. Rather the symptoms can be either of: a) the macro isn't run at all or b) the 'caller' object in the called macro is invalid.