Running macro using buttons is causing problems with menus

Question

Hi, Hopefully someone has seen this problem before.

I have an Excel workbook which has some buttons (shapes) that have macros assigned to them, which switch between worksheets.

If I run the macros from Visual Basic window (run, not step into) it works well. These exact same macros have been assigned to buttons in the worksheets. If I click them to switch between the sheets, after a few clicks the menus (such as changing font, clicking on my user information, sort/filter) become disabled. I am able to click on the menus but then a shadow like box of what should be displaying shows up (attached image showing when I click on my user info). And even more strange, it is just on certain sheets.

I can confirm the workbook and worksheets are not protected. Some of the worksheets have Worksheet_Change subs but I get the same problem on sheets that do not. The only ThisWorkbook sub I have relates to BeforeSave.

Any help would be appreciated.

jkpieterse · Answer

Can you share some of the relevant code please?

joeskh · Answer

JKPieterse&nbsp;Many thanks for your interest.&nbsp;&nbsp;I have made a scaled down version of my file which I have attached, which highlights the problem I am having. From my testing, it would suggest that Excel has a problem when switching sheets between visible and invisible when a macro is used to run using a button that is a shape or a form control.&nbsp;In the attached, the main sheet has multiple ways of switching to page 1 and page 2. The first set of buttons are shapes. The second set form controls. The third set ActiveX controls. The fourth set are using the Worksheet_SelectionChange.&nbsp;Use the Shapes or Form Controls, try clicking to go to page 1, then come back, then go to page 2 and then come pack. You should find the many of the menu items are not working. But if you run the macros by using the ActiveX controls, the&nbsp;Worksheet_SelectionChange or other manual ways of running the macros, I dont seem to have the same problems.&nbsp;I tested&nbsp;the Shapes or Form Controls buttons, where the macro did not need to make the sheets visible/invisible and the problem with the menus did NOT exist. Hence I think it relates to the changes to the visibility.&nbsp;&nbsp;I am using Windows 10 64 bit, all updates done. Office365 32bit, all updates done.&nbsp;

jmb17 · Answer

joeSKH&nbsp;&nbsp;One way that could happen is if you left click on a command button, but then move the mouse off the button before letting up the mouse button. Then the command button has the "focus" (you'll see a dotted line around the button indicating it is selected). When a button has the focus, ribbon functions that cannot be used on a button are grayed out.&nbsp;I also noticed that, after clicking around for a little bit, when I click on the "Page 2" blue rectangle on the Main worksheet that the command button on Page 2 is the image of the Page 1 command button (see Capture1). The command button for Page 2 is there, but you have to left click where it's supposed to be (Column I) and then it shows up (see Capture 2, which also shows the ribbon grayed out because the command button has the focus). Not&nbsp; sure why it's doing that, though.

jkpieterse · Answer

I don't see any weirdness on my end. It all works as expected.What happens if you launch Excel in safe mode and then try?

joeskh · Answer

In safe mode I do not experience the issue.&nbsp; I was able to replicate the same problem on my fellow employees computers, but they would have a very similar set-up to mine. We dont have any add ins active except for Acrobat PDF Maker and based on my reading, with it being a COM Add in it is not excluded by safe mode. The only settings that we have changed from default are Trust Center settings above ActiveX and Macros - I tried changing these back to default but it had no effect.&nbsp;&nbsp;I have an old laptop with Excel 2010 and it did not have the problem.&nbsp;&nbsp;

Forum Discussion

Running macro using buttons is causing problems with menus

5 Replies

Resources