Custom macro buttons not working - quick access toolbar or ribbon

[iliace]

Hi everyone,


Excel 2010, Office Pro Plus, Windows 7, everything 32 bit.


I've seen this asked before around the Internet, but did not see any good answers. Now I am experiencing this issue and it's driving me crazy.


I have a large number of macros, stored in PERSONAL.XLSB, that I have assigned over time to buttons on my quick access toolbar (QAT). One day, all of them stopped responding. The macros still work just fine, and in the Customize menu it says the correct path and name for each one. But clicking the buttons has no effect.




I have tried removing and re-adding the buttons, and resetting the toolbar - doesn't work. No new (or old) macros added to the QAT run. I exported the toolbar, and examined the XML - appears fine. Then I tried to import the same file on another machine, and it worked there. So it's isolated to just one workstation.




Also, I tried to create a custom ribbon tab, and add macros there. Those don't work either.




Customizing built-in commands works just fine, existing ones and any new ones added. Only macros don't work.




I hope someone has dealt with this problem successfully in the past Appreciate any suggestions.

 

[iliace]

Hi iliace,

I had same problem but found out that this problem was happening while also my personal.xlsb file was not starting automatically when starting excel.
This was solved by checking my "disabled Items" in Options-Add-Ins-Manage Disabled Items (at the bottom of the page)-Go. Here my personal.xlsb file was shown as disabled and I could choose: enable
Now the personal.xlsb file is starting up automatically again and also my QAT macro-buttons are functioning!

Also check Personal Workbook Fails to Load (Microsoft Excel).

Hope it helps as well for anyone having this problem

Thanks for sharing. Eventually, I did figure out this as well. Not sure how or why it happens, but definitely a place to check if you're having this issue.

 

[jcrosland]

I had this same problem, after enabling ALL Macros, checking my disabled items list (my personal.xlsb was NOT listed here) I remembered... this all started after I exported my QAT. So, I simply IMPORTED the 'Excel Customizations.exportedUI' file by right clicking on the QAT, clicking on the 'Customize Quick Access Toolbar' then in the pop-up window, in the bottom right hand corner, there is an 'Import/Export' drop-down menu. This is where I had originally exported the QAT. After Importing this file... all my customized Macro buttons on my QAT were working again.

Hopefully this helps someone else... as I, too, was in despair

 

[TrojanPryd]

Hi iliace,

I had same problem but found out that this problem was happening while also my personal.xlsb file was not starting automatically when starting excel.
This was solved by checking my "disabled Items" in Options-Add-Ins-Manage Disabled Items.....


That was an awesome suggestion! Totally helpful especially to a newbie like myself. Awesome! Thanks a bunch.

 

[ilyvba]

Thank you for sharing this!

 

[PJinSanAntonio]

Dear Iliace,

Thanks SO much for the info you shared! As I have changed jobs, I have moved around to several new computers, and I have always moved copies of my macro files, which are usually located at C:\Users\[your user name]\AppData\Roaming\Microsoft\Excel\XLSTART\, and my Excel User Interface file (which contains the info for the QAT (Quick Access Toolbar) and any custom macro buttons I have inserted in the QAT), Excel.officeUI, usually located at C:\Users\[your user name]\AppData\Local\Microsoft\Office\, to my new computer, so I don't lose all my macros or the work I spent setting up custom macro buttons on the QAT.

However, when I recently placed these files on my laptop at home and tried to run macros from custom macro buttons in my QAT file, I was getting an error message of "Sorry, Excel cannot open two files with the same name at the same time" (or something very similar). This really frustrated and confused me, because I had never seen this problem before until, I think, I started using Excel Professional 2016. By using the info you provided, I finally discovered the problem: the username I had on my work computer was different than the one I'm using on my laptop, and Excel Professional 2016 was looking for my macro file in a folder with the user name I had on my work PC:

C:\Users\SpaceCadet\AppData\Roaming\Microsoft\Excel\XLSTART\

...instead of the user name I had created on my laptop:

C:\Users\TechGeek\AppData\Roaming\Microsoft\Excel\XLSTART\

Apparently Microsoft has changed the way the Excel UI file records info for custom macro buttons you insert in the QAT. The Excel UI file now records the EXACT location of the macro file containing your macro at the time you add the custom macro button to your QAT. So, if you transfer your macro files and Excel UI file to another computer and then try to run one of your macros from a custom button on the QAT, it is going to look for the EXACT location where the macro file was when you first added the macro to the QAT bar. If you have changed computers and your username on the new PC is not the same as it was on the old PC, none of your macro buttons in your QAT are going to work.

I don't think MS had done this before with Excel UI set up files, because I had taken my macro files and Excel UI setup file with me from a couple of previous computers to new work computers, and everything worked fine.

What I found especially helpful in your info was that you can edit your Excel UI set up file using Notepad!!! I was able to go to C:\Users\[your user name]\AppData\Local\Microsoft\Office\ on my laptop, open the Excel UI file, Excel.officeUI, with Notepad and then replace the user name from my work computer with the username I use on my laptop. After saving and closing the Excel UI file--voilá! Back in business! Everything works like a charm!

Taking the time to document this, so that anyone else who runs into this problem hopefully won't have to spend time searching around the web to find the solution.

Gratefully,
PJ ;oP

 

[iliace]

I don't think MS had done this before with Excel UI set up files, because I had taken my macro files and Excel UI setup file with me from a couple of previous computers to new work computers, and everything worked fine.

What I found especially helpful in your info was that you can edit your Excel UI set up file using Notepad!!! I was able to go to C:\Users\[your user name]\AppData\Local\Microsoft\Office\ on my laptop, open the Excel UI file, Excel.officeUI, with Notepad and then replace the user name from my work computer with the username I use on my laptop. After saving and closing the Excel UI file--voilá! Back in business! Everything works like a charm!

You're welcome. I may be remembering incorrectly, but I think I started having this issue around Excel 2013. I don't think this was the case in Excel 2010 (my personal favorite version).

I also discovered that you cannot use %APPDATA%, Excel treats it literally. But, you can remove the path altogether, in which case as long as your PERSONAL.XLSB is already opened it will work just fine. Then, if you re-export it to take to another machine, they will stay that way. However, if you added any new buttons, Excel will still remember the full path and you'll need to do the find/replace bit.

 

[Ronmio]

I too had been suffering from dead macro buttons on my QAT. "florianvk" exactly identified the problem ... my Personal.XLSB had been disabled. More importantly, he/she/them led me directly to the solution ... File>Options>Add-Ins>Manage:>Disabled Items (from the drop-down list at the bottom of the page)>Go>Enable. That promptly fixed the problem.

I suspect this well-hidden disabling was the result of a Windows/Office update as the problem appeared in Excel on all my computers at roughly the same time (in 2020, four years after florianvk's post).

 

[Giel V]

I was able to come up with a workaround. It looks like the problem only occurs in the PERSONAL.XLSB workbook; more importantly, any other file saved in the default XLSTART folder does not experience the problem.

I exported all the modules from my PERSONAL.XLSB file, and imported them into a new file, called Macros.xlsm. I saved the file in the same location as PERSONAL.XLSB. I also exported by Quick Access Toolbar using the built-in Export feature; I then opened the file in a text editor (Notepad++) and did a find/replace to change PERSONAL.XLSB to Macros.xlsm. I saved Macros.xlsm in my XLSTART folder. Once I opened Excel, I imported the original modified toolbar file into my QAT.

And guess WAT! (pun intended) Now all my macros work again just as before - it is beautiful!

This is a decent workaround - simple enough, and without too many changes to "business as usual". However, it leaves many questions unanswered. Why did my shortcuts suddenly stop working? Why is it that only my PERSONAL.XLSB was affected? Why do the exact same files work perfectly fine on other computers, but not on this one? Why did the macros work from other interfaces, but not from the QAT? Why did this only happen for Excel, and not other Office programs? All are good questions deserving of a good answer. Please share if you have gone through this experience.

Thank you for the explanation. This was my problem and your sollution. Still valid today!

 

[MHShoman]

Hi iliace,

I had same problem but found out that this problem was happening while also my personal.xlsb file was not starting automatically when starting excel.
This was solved by checking my "disabled Items" in Options-Add-Ins-Manage Disabled Items (at the bottom of the page)-Go. Here my personal.xlsb file was shown as disabled and I could choose: enable
Now the personal.xlsb file is starting up automatically again and also my QAT macro-buttons are functioning!

Also check Personal Workbook Fails to Load (Microsoft Excel).

Hope it helps as well for anyone having this problem

thanks, that solved my issue