ActiveX buttons unresponsive or having to be clicked twice to run macro

[Celia_Alves]

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.

[#2]

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.

[#3]

Why don't you want to use the forms button?

[#4]

@Jan Karel Pieterse 

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.

[#5]

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.

[#6]

I see, thanks for the guidance.

Could you give me an idea ofnhow data validation would work for data entry?

In my case I am using the button to work through a series of if statements that read values from several cells and produce a text string in another cell as a result. Not sure how to otherwise perform that task without an on demand button.

[#7]

@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.

[#8]

@PratikAngaj That worked! Thank you so much.

[#9]

@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.

[#10]

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.

[#11]

@PratikAngaj This works perfectly for me. Thank you very much for sharing

[#12]

Thank you! I had a new workbook and found the same issue with my buttons. Your solution worked perfectly.

[#13]

Pratik's answer worked for me. No other change made. Thank you Pratik!

[#14]

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.

[#15]

@PratikAngaj Love U, thanks a lot.

[#16]

@digibrain 

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.