Excel Excel 2007: Macro Quick Access Toolbar Not Working

[excellerator]

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.



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

 

[excellerator]

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

 

[jmmach]

Did you save the file as an .XLSM?

Are the macros still in the file?

JMMach

 

[excellerator]

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.

 

[STFARRAR]

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

 

[jmmach]

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...cess-toolbar-and-customize-button-images.aspx

 

[STFARRAR]

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

 

[si24392]

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!

 

[STFARRAR]

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?

 

[si24392]

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.

 

[excellerator]

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