Can't click ActiveX CommandButtons in Excel 2016 and they are not appearing correctly

Question

I have several spreadsheets with ActiveX CommandButton objects that I used to invoke my macros.  These all worked fine up until a few days ago (~ 13 Jan 2019) when strange issues started to occur.

The buttons will now only respond intermittently to clicks, and sometimes not at all.  If they do start to respond, it is only briefly and then they stop responding again.  The weird thing is that the buttons do continue to respond correctly and consistently to the associated accelerator keys.

In addition, the buttons are now not being painted correctly.  The 3 strange looking rectangles in the image below are the buttons.

This issue is occurring across multiple spreadsheets.  I am running Excel 2016 32-bit under Windows 10 on an MS Surface device.  It is a monthly-subscription installation so updates are being automatically applied.  I can't see anything in the history of the updates that might obviously be related.

Any advice or news from others suffering the same issue would be appreciated.

David.

Answer 1

Hi David,

I am Vijay, an Independent Advisor. I am here to work with you on this problem.

Please try these options one by one and check after each option.

1. Sometimes, add-ins can cause problems. To determine, if add-in is a problem start your application in safe mode and see if problem has gone away.

To start in Safe Mode -

Hold CTRL key and click on application icon and don't release the CTRL key till it asks you for Safe mode confirmation.

If yes, then start application normally and disable add-ins one by one and start application again every time you disable an add-in to determine the culprit add-in.

To disable add-in > File > Options > Add-ins > Down below you have Manage: Com Add-in : Press Go > Untick the Add-ins to disable and tick to enable them

2. Please look into C:\Users\User_Name\AppData\Roaming\Microsoft\Excel (replace user name with your user name, you can directly reach here by copy and pasting following without quotes in Explorer address bar - "%appdata%\Microsoft\Excel") and see if is contains few files / folders. Move those files to some other location (i.e create a backup of those files / folders and delete all files / folders from here). Hence, make Excel folder blank.

Now open Excel and see if the problem disappears or not.

Note - If the above path is not applicable for your installation, you need to find XLSTART path. Excel folder contains XLSTART folder in itself (In case, your XLSTART path is not in Appdata)

Open Excel > ALT+F11 > If Immediate Window is not visible, type CTRL+G. Same can be also be accessed through View > Immediate Window > Type "? application.StartupPath" without quotes and press enter > You will have your path below.

3. Try repairing your office installation (Quick repair is good enough). Try repairing your Office installation. First try Quick Repair (takes 5 - 10 mins) , if that doesn't help then try Online Repair (Takes more than 30 mins but less than 1 hour in general)

https://support.office.com/en-us/article/Repair...

Do let me know if you have any more question or require further help. If above didn't work, I will supply more troubleshooting steps to solve this problem.

Answer 2

Hi Vijay,

Thanks for the prompt response.  I followed your steps (point 2 specifically) and have found that the PERSONAL.XLSB file that I recently created appears to be triggering the problem.  That file only has a couple of macros that I've created for general use and there's nothing in them that is obviously directly related to the problem I'm seeing.

That file is located in the following directory:

C:\Users\david\AppData\Local\Packages\Microsoft.Office.Desktop_8wekyb3d8bbwe\LocalCache\Roaming\Microsoft\Excel\XLSTART

Removing that file appears to allow the buttons to work properly again.  Obviously, removing that file is not the ideal solution for me.  Any ideas on what to do from here?  It appears to me that there is some defect in Excel that I've encountered.

Regards,

David.

Answer 3

Create a blank Personal.xlsb again and copy these macros one by one manually into this new Personal.xlsb.

To create a new personal.xlsb - Record a macro, it will ask in Macro Window, its save location, choose personal macro workbook. Stop macro and you will have a new personal.xlsb. Now delete the recorded macros and copy old macros into this.

Answer 4

That worked Vijay.  Buttons are behaving normally again and my personal.xlsb file has the original macros back in it.  Still unclear why this problem occurred and am wondering if it will happen again.  We shall see.

Thanks,

David.

Answer 5

Great! I am glad that worked! If you don't have any more questions, feel free to choose a rating and have a great day!