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: