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]

Update: apparently, only the macros in my PERSONAL.XLSB do not work from the Quick Access Toolbar. If I add a Sub from a new workbook, or an add-in, it works just fine.

I tried to delete and re-create the PERSONAL.XLSB. In a brand new PERSONAL.XLSB, I created a simple macro (MsgBox "Hello World"). It still doesn't work when added to the QAT, but runs just fine when executed from VBE or Alt+F8 macro dialog box.

I also exported both the PERSONAL.XLSB and the QAT customization, then installed both on another workstation (on the same network). It works just fine! So the problem is limited to just the one computer, and only Excel and not the rest of Office; on top of that, it is limited to just PERSONAL.XLSB macros, because everything else seems to work without a problem. WHY?!

Earlier, per help desk suggestion, I attempted to "repair" the Office installation. Didn't help. The guys from IT also rolled back a security update (KB2553154), which changed absolutely nothing.

I am at a complete loss, and in a horrible pit of despair (not actually, but I will go ahead and play the victim in this situation). Any thoughts?

 

[iliace]

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.

 

[jrichter]

Hey there, I am having a similar situation. However, my problem affects other files also in the XLSTART folder. I have a large macro that many users at my job utilize, and so I made a new file (instead of using PERSONAL.XLSB) to hold the macro. I ask all users to save this file in their XLSTART folder and I help them set up a shortcut in a new ribbon tab for ease of use. All has been fine until the past few months, and people are now complaining that their button no longer starts the macro. No error, just nothing. It seems to be related to some Windows updates that come down, the poof, buttons no longer work. Most users are running Excel 2010, as am I, and I do not have the problem? Its confusing. I hope someone has seen this and has an answer.

 

[iliace]

During the fiasco outlined above, our tech support attempted to roll back some security update - I lost the number, but it's related to "potentially malicious code". It didn't fix the issue.

I also found this suggestion: "At startup, open all files in" has stopped working - Microsoft Community I went to look in my Disabled Items list, and lo an behold: personal.xlsb is disabled.

Still not sure why it still worked from VBA (but not the QAT buttons), or what caused it to become disabled in the first place.

You might also check the "At startup, open all files in" (under Excel Options->Advanced->General), although I believe XLSTART is included by default.

You might also consider distributing your file as an add-in, rather than having users put it in their XLSTART folder.

 

[jrichter]

Thanks a bunch for the advice. I will ask users to check these things. I have heard other mentions of saving the file as and add-in, can you provide some more detail on that? I am a bit clueless on that option.

 

[iliace]

Basically, you create a workbook, put your macros in there, and save it as a .xlam (add-in) file. Users can then import it from the Options->Addins->Excel Add-ins and it will load automatically whenever they start Excel.

 

[jrichter]

Nice. Sounds easy enough, thanks a ton!

 

[austin_love_32]

Just wanted to say thanks! I had the same problem and you just saved me a major headache.
Although I guess I have to go and be productive again.

 

[florianvk]

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