3

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.
Assign Macro Popup

When clicking the Shape to run the assigned macro I get this error message.
Error Message on clicking shape to run Macro

Improve this question
3
  • To clarify, the code is in a regular VBA Module, not in the "ThisWorkbook" section. Commented Nov 9, 2021 at 17:47
  • Screenshots added to original post. There is no code in ThisWorkbook module Commented Nov 9, 2021 at 21:13
  • 1
    Solved - turned out I needed to check the box for "Enable Excel 4.0 Macros". Apparently, Microsoft has just started disabling this by default in Office 365 starting this month! I posted an answer as the resolution. Commented Nov 10, 2021 at 22:22

4 Answers 4

Reset to default
3

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:

Code in sheet

Assign Macro:

Assign Macro

Button press:

Button press

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Sorry I should have been clearer. The code is in a regular VBA code Module, not in the "ThisWorkbook" section. I meant I assigned a Macro to the button from "ThisWorkbook" on the assignment popup window.
Good to know I should not include _ in VBA names. Didn't know that. Anyway I removed the _ and have same issue
@epe Please edit your question to include a screenshot of where your code lives and a screenshot of the button binding. These are controlled by the gui and not code itself.
1

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.

Improve this answer

1 Comment

Good thought. In my case, it's just a laptop with no extra screen attached. Scaling is 250%, as the standard recommended setting. 100% is tiny font on a 4k screen.
1

Solved - turned out I needed to check the box for "Enable Excel 4.0 Macros". Apparently, Microsoft has just started disabling this by default in Office 365 starting this month!

So in Excel... File>>Options>>Trust Center>>Trust Center Settings>>Macro settings

Improve this answer

10 Comments

You should not be using Excel 4.0 macros. They are quite different from VBA. They are also insecure. This option needs to be disabled at all times. Only use VBA. The solution I provided is a VBA solution that doesn't required this unsafe setting.
Agree. I don't think I am using Excel 4.0 Macros, as far as I know. I don't even know what they are. I just have regular VBA and am following all your suggestions in your post HackSlash. All I know is that when i enable Excel 4.0, the buttons started running my VBA code again. And when I disable it, the buttons stop working. (BTW, how can I tell if I have any Excel 4.0 Macro code?)
You would know. It's code written in to cells, like a formula. It doesn't use the VBA window at all.
Ok good - thanks. I'm not using Excel 4.0 Macros then. No idea why I have to enable it to make my buttons work, but such is life
Am going to test this too: my buttons and checkboxes keep on disconnecting from their macros too (the buttons in the custom menu bar keep on running those same macros without issues).
|
0

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.

Improve this answer

1 Comment

Good idea. I ran a program to look for dups and found a couple and renamed them, but didn't fix the issue

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.