Excel Excel 2007: Macro Quick Access Toolbar Not Working


Joined
Jun 17, 2009
Messages
5
Reaction score
0
First time poster so bare with me.

My issue is: My customised QAT in Excel 2007, that includes 'not in ribbon' buttons, doesn't save when I add a macro button for default or specific docs.

:confused:

The macro button and the macro will work fine when I add it, but when I close and reopen the file or email the file, the button has gone :(

The bizarre thing is, it was working a few weeks ago. :eek: I even had some customised macro buttons in the file that I was trying to add more to, and when I saved and reopened the file, ALL the custom macro buttons disappeared.

I've already searched google for days now trying to find a solution.....

I've read ron debruin's site back to front http://www.rondebruin.nl/qat.htm and http://www.rondebruin.nl/qat2.htm

The instructions are there for the process but no solution as to why the button wont stay put.

I'm running Windows 7, and obviously Office 2007. Also, this is a work PC on a network (I'm not the network admin). The network admin hasn't done anything to the system that he thinks would have done the changes i'm whinging about.

Any help, ANY!!!!, will be greatly appreciated.

Thanks

D
 
Joined
Jun 17, 2009
Messages
5
Reaction score
0
WoW

I've either not explained myself properly or i've stumped even the brightest of PCReview forumers.

Guess i'll have to live with the issue :(

Thanks anyway
 
Ad

Advertisements

Joined
Jul 17, 2009
Messages
3
Reaction score
0
Did you save the file as an .XLSM?

Are the macros still in the file?

JMMach
 
Joined
Jun 17, 2009
Messages
5
Reaction score
0
Yes and Yes.

I've tried saving the workbook with the macros as everything thats available.

I've deleted the QAT folder in the attempt to reset it but nothing.
 
Joined
Jul 23, 2009
Messages
3
Reaction score
0
I do not have a solution but recently encountered the same problem- please post an update if you find out the solution- I have looked everywhere I can think of
 
Joined
Jul 17, 2009
Messages
3
Reaction score
0
The fact that you can customize buttons, and they work, but disappear when you close and reopen Excel, suggests that when it tries to write the excel.qat file it fails to update the file. So it would be worthwhile to check permissions to the folder where excel.qat is stored:
In Windows XP you will find it here:
C:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\OFFICE\Excel.qat



Try some of the suggestions in this blog:
http://blogs.msdn.com/excel/archive/2009/07/17/add-buttons-to-the-quick-access-toolbar-and-customize-button-images.aspx
 
Ad

Advertisements

Joined
Jul 23, 2009
Messages
3
Reaction score
0
Not sure that's the problem- according to something I read (Ron deBruin ?) the settings for qat toolbars that are unique to a workbook are not kept in the excel.qat file but in the workbook itself- the xlsm files are actually zip files that contain a file names customeui.xml:



It will save the setup in the “userCustomization” folder in a file named “customUI.xml” inside the
"OneWorkbookQAT.xlsm" file. You can read more about this in the example "Change QAT button Image".
 
Joined
Aug 12, 2009
Messages
2
Reaction score
0
You have to remove all the paths inside the customUI.xml file and leave the macro names only.

Detailed Solution:

Rename your file with a .zip extension.
Open the customUI.xml file in the userCustomization folder.
For every button you have, there will be an "idQ" property.
That property will have the full path of the macro.

Remove the whole path except for the macro name so the format is like this:
idQ="doc:MacroName_1"
(you want to leave the "doc:" and the "_1" there)

Also, remove the path from the onAction property:
onAction="MacroName"

That's it. Save the file, change it back to an .xlsm file, and the buttons are there!
 
Joined
Jul 23, 2009
Messages
3
Reaction score
0
Excellent- thanks for the help

One little problem- I'm not that familiar with editing zip files- I right click it, choose edit, make the changes and then save- when it does that it tells me that it is being saved without folder information.

How do I get it to save in the correct folder?
 
Joined
Aug 12, 2009
Messages
2
Reaction score
0
Use an extracting program like 7-zip. Extract the zip file to a folder, edit the customui.xml file within that folder, save it, then zip the whole folder again.
 
Ad

Advertisements

Joined
Jun 17, 2009
Messages
5
Reaction score
0
si24392 = hero!

It Works!!!

I know it was mentioned in Rons walk through but I didn't think to implement it.

I can now be lazy and save 3 button clicks ;)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top