Forum Discussion
Running macro using buttons is causing problems with menus
JKPieterse Many thanks for your interest.
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.
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.
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 Worksheet_SelectionChange or other manual ways of running the macros, I dont seem to have the same problems.
I tested 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.
I am using Windows 10 64 bit, all updates done. Office365 32bit, all updates done.
- 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?In safe mode I do not experience the issue. 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.
I have an old laptop with Excel 2010 and it did not have the problem.
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.
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 sure why it's doing that, though.
