http://fracta.net/fracta/index.php/forum/2-excel-vba-forum/19-customizing-excel-2007-ribbon-menu-by--CSDN博客

TOPIC: Customizing Excel 2007 Ribbon menu by adding your own tab and a button to trigger a macro

Customizing Excel 2007 Ribbon menu by adding your own tab and a button to trigger a macro 1 year, 5 months ago #19

Customising the menu in Excel 2007 is different to previous versions of Excel. You will notice that if you use VBA code from previous version to 2007 the new menu item it will appear under a new tab called Addins. If you want to add your own custom button and Tab in Excel 2007 you will have to do it using XML.

If you haven't worked it out yet, Excel 2007 files are a zip folder with an extension .xlsx, these zip folders contain many files that make up the Excel document. To view what I am talking about grab any Excel 2007 file and add .zip at the end. For example if your file name is sales.xlsx name it sales.zip and open it using WinZip. You will see a whole bunch of folders contained in this zip file, brows thru them and discover what they contain.

Now that you are familiar with the structure of the Excel 2007 file I will explain how to add a custom Ribbon button and tab with a simple example.

First create a new folder and name it customUI, place it next to the other folders which may be called xl, docProps, _rels.

Inside the customUI folder you just created create a file and call it customUI.xml, you can use notepad to do this.

Inside the customUI.xml file add the below XML code, I will explain the code later.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="EdTab" label="Edgar Add-ins">
<group id="group1" label="Group 1">
<button id="myButton" label="Add Rows" screentip="Click to add rows between each row in a new book" onAction="myButton_ClickHandler" /> 
</group>
</tab>
</tabs>
</ribbon>
</customUI>

After you added the above code go back to the other folder contained in the Excel zip folder and go inside the folder called _rels, there you should find a file called .rels.

If you open this file you will see something like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml" /> 
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml" /> 
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml" /> 
</Relationships>

You will need to add one line of xml code and just before the </Relationships> tag, the line to add will be

<Relationship Id="edTAB" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml" /> 

When finished the .rels file will look like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml" /> 
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-prop erties" Target="docProps/core.xml" /> 
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml" />
<Relationship Id="edATM" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml" /> 

</Relationships>

The Relationship Id ="edTAB" above can be changed to any vlaue that is unique, I chose edATM because of what my macro did. Feel free to name it whatever you like but make sure that it is unique and not likely to be used by Excel anywhere else, be creative. This line of xml tells Excel where to find the custom ribbon stuff we added before (Target="customUI/customUI.xml"), that's all it does.

Now if you rename the file back to sales.xlsx and open it with Excel you will see a new tab called Edgar Add-Ins to the right of the Home, Insert, Page Layout, etc.. tabs. This is defined in <tab id="EdTab" label="Edgar Add-ins"> in the above code.

If you click on that tab there will be a group named Group1 defined with <group id="group1" label="Group 1"> with one button called Add Rows defined with <button id="myButton" label="Add Rows"..

If you hover your mouse over the new button you will see "Click to add rows between each row in a new book", defined with <button id="myButton" label="Add Rows" screentip="Click to add rows between each row in a new book" onAction="myButton_ClickHandler" />.

To make this button trigger a macro add the below code to a module in the VBA editor:

Sub myButton_ClickHandler(control As IRibbonControl)
Msgbox "you clicked me, I am a demo and will not add rows between each row"
End Sub

If this seems like a lot of work to add one button you can find tools to do it available on the internet, they can make things a lot easier. But if you are a nuts and bolts kind of person and like to work things out this will be a bit of fun and you will get to understand the Excel 2007 files structure a bit more.