Apr 01 2019 09:12 AM
Hello, community!
I inherited two Excel files that come with a bunch of ActiveX buttons.
In some cases the buttons don't do anything: I click them, it shows kind of a duplicate image of the button on the side when I press the button, but nothing happens. The macro associated runs well if I run it from the VB Editor.
On the other file, the buttons do work but only when I click them for the second time. Any idea what may cause this to happen?
Thank you in advance for any tip or guidance.
Jan 15 2020 07:10 AM
I have also tried these solutions but nothing works...
My activex button works the first click when i open excel, but stops working after that. Running the macro from inside VBA project works fine. The button seems to lose its connection with the macro. Driving me crazy, and I really don't want to replace with Form Control.
Jan 15 2020 12:48 PM
Jan 15 2020 02:34 PM
Let me preface my response with the fact that I am new to VBA and coding in general. My approach to learning new things is to learn as much as I possibly can and to use tools that allow the most flexibility. My understanding is it ActiveX controls are more dynamic than form controls and so that's what I started using. At this point I'm more interested in understanding what's going on rather than identifying a workaround. I certainly could use the form buttons if I just wanted it to work.
I've also tried deleting the .exd files from Temp, restarting PC, copying everything to a new file. Other Active X buttons (linked to a different module but in the same manner) work without issue. Just this one (and its multiple copies across sheets) doesn't work.
I created a new button in a blank sheet in the same workbook and it runs the macro every time. The buttons on the original sheets still fail.
If I change the button event to MouseDown (from Click) it works.
Even if I edit the button click event sub to only include a MsgBox (rather than Application.Run "module...) it doesn't even work.
Jan 16 2020 12:57 AM
Hi @Anammox
Well, it is oddities like this one that keep me from using ActiveX controls. Unless there is specific functionality only these controls have to offer (like events other than click) I keep away from them. And even then, the Forms controls too have their issues. I also do not like using controls to build data entry functionality. Data validation is more robust and also works on different platforms like Excel on-line.
Jan 16 2020 07:10 AM
Apr 21 2021 12:35 AM
@Celia_Alves I had the same problem and I solved it by changing the display setting.
on the excel status bar > Display Settings> choose : optimise for compatibility.
issue will resolved.
Apr 21 2021 05:41 AM
@PratikAngaj That worked! Thank you so much.
Apr 29 2021 02:00 PM
@HarveyWatson I don't want to beat a dead horse here, but it happened to me as well. I didn't see @pratikangaj response or I would've tried that first...
Anyway, I went to design mode, right clicked on the button and selected "Command button object", then "Edit". I didn't change anything, just clicked out of that and for some reason that fixed it.
Apr 30 2021 06:26 AM
The problem seems to have gone away for me now. Don't know if that's because I did the delete .exd files thing or because, perhaps more likely, MS have now fixed whatever it is they did to cause this in the first place.
Apr 07 2022 12:02 AM
@PratikAngaj This works perfectly for me. Thank you very much for sharing
Jun 16 2022 09:57 AM
Jul 08 2022 07:13 PM
Mar 07 2023 08:04 AM - edited Mar 07 2023 08:08 AM
Excellent! This improved the behavior a lot. Still, for the ActiveX button to work on the first click, the excel workbook has to have focus already. Otherwise, the first click changes the focus to excel and the second click runs the ActiveX macro. It looks like by disabling 'best appearance' we lose just that: the worksheet appears not at its best. So, it's a bit of a trade-off.
Jan 31 2024 05:14 AM
@PratikAngaj Love U, thanks a lot.
Apr 12 2024 06:30 AM
Solution of PratikAngaj works also for me, but I really don't like the degraded look of the worksheet.
Another solution I found is to modify the name of the macros in the VB editor (Alt-F11), so as to use the "MouseUp" event or "MouseDown" event, instead of the "Click" event.
For each control:
- replace the "ControlName_Click ()"
- by "ControlName_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
No more trade-off :)
Maybe it is also possible to add something to the Class, but I'm not sharp on this.