Microsoft Excel365 ActiveX controls not working

[revelation.now]

Hi All,
I'm trying to replace some ugly grey buttons in a document with ActiveX buttons. If I add a button, put some code behind it, exit Design mode and click on the button, rather than executing code a smaller copy of the button appears above and to the left of the button.
If I then save the document and reopen it, sometimes the button starts working, but usually not.
Heres an example of the code I'm trying to run:

Private Sub CommandButton1_Click() 
Sheets("Sheet2").Visible = True 
Worksheets("Sheet2").Activate 
End Sub

I've tried deleting all exd files from my computer. Is this a known issue and is there a work around? I see Microsoft broke ActiveX controls in Excel back in 2014, but I would have thought 4 years later they would be fixed?

 

[Jon Peltier]

What version of Excel are you using? There is no such version as Excel 2014, by the way.

ActiveX controls do not work in Mac Excel, and most people, myself included, will advise you to use controls from the Form menu instead of ActiveX because of reliability.

 

[revelation.now]

What version of Excel are you using? There is no such version as Excel 2014, by the way.

I'm using Office365 Pro (as per the subject) which basically a 'cloud' version of the old 'year numbered' versions of Office. There is an Office 365 for Mac which you might be more familiar with.

Current version is 1901 (Build 11231.20130)

I mentioned 2014 in reference to the year in which I can see Microsoft last broke ActiveX, and that applies to Office 2007, 2010 and 2013 which were the available versions of Office at the time (which was 2014). Unfortunately most of those fixes won't apply given that Microsoft never made a fix for this for later versions of Office (such as 2016 or 2019 or Office365)

 

[Jon Peltier]

Sorry, I missed the reference to Office 365 in the subject line. That's what I'm using too (to be precise, build 11330.20014).

ActiveX controls were badly broken in Excel 2007, which was more an issue with Office 2007 rather than ActiveX, but by SP2 they weren't quite so bad. But in general, as I noted, ActiveX controls can be rather flaky, despite nominally looking nicer and being more full-featured than Form controls. This was true in Excel 2003 and earlier, and it's been true in Excel 2010 and later. But when my clients use them, then so do I.

And so I duplicated your situation. I made a workbook with Sheet1 and Sheet2, then I hid Sheet2, and created CommandButton1 on Sheet1. I right clicked on the button, and inserted your two lines of code into the procedure stub that Excel inserted in the Sheet1 code module.

Then, the moment of truth. I turned off Design Mode, then clicked the button. Sheet2 became visible and then was activated.

I can think of a couple things that may have gone wrong. Make sure the button name matches the procedure name. And don't type the entire procedure yourself, but use a right click to view the code, which inserts the appropriately named procedure in the appropriate code module, usually the module behind the sheet that contains the button.

 

[revelation.now]

I can think of a couple things that may have gone wrong. Make sure the button name matches the procedure name. And don't type the entire procedure yourself, but use a right click to view the code, which inserts the appropriately named procedure in the appropriate code module, usually the module behind the sheet that contains the button.

* the code has been auto gen'ed, I've just entered the two statements
* the function matches the name of the button given that it was auto-gen'ed.

Again, I can sometimes open this 'sample' document and have the ActiveX control work. I don't need to change anything, maybe just restart the computer.

I was handed this task by a less experienced developer who had the same problem on his computer, and our computers come from different images and manufacturers - the only consistency is that we are both using Office365 rather than a retail release, and we're both using Windows 10.

I've attached an image to illustrate this a bit better. In the linked document there is 1 button. You can see 2 buttons, but thats what happens most of the time when these ActiveX buttons are clicked.

Unfortunately, there appears to be a bug in the mrexcel.com forum that prevents the image link embedding from working. Here is the raw URL:

https://www.dropbox.com/s/e25i8wfzej5bvnx/brokenexcel.png?dl=0

 

[Jon Peltier]

Yeah, that's a strange one. Are there lots of ActiveX buttons? I would try deleting them, saving and closing the workbook, then adding them back. Kind of like turning them all off and on.