Adding a ribbon item...excel '07

[Paul_Hossler]

Micro$oft spent billions of dollars and millions of person-hours to change the interface, just to give us all something to do in our spare time

The little bit below does NOT even begin to scratch the surface.


Well, FWIW (bold, underlined, big type) when I had to convert an XLS from 2003 to 2007, I just started over with the design to use the UI and not to fight it

1. I went to

http://openxmldeveloper.org/articles...muieditor.aspx

and got a copy of the CustomUI Editor. It's a little quirky, but works pretty well, and it's free. Also check out the links and comments


2. Created a new XLSX and saved it as a XLSM.

3. Used the editor to open it, and started with one of the Samples ("XL - Custom Tab")

4. It made some boiler-plate XML (the header and the 'customTab' below)

5. I added the customTab1 XML to create a demo XLSM for you.

[vba]
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="customTab"label="Contoso"insertAfterMso="TabHome">
<group idMso="GroupClipboard" />
<group idMso="GroupFont" />
<group id="customGroup"label="Contoso Tools">
<button id="customButton1"label="ConBold"size="large"onAction="conBoldSub"imageMso="Bold" />
<button id="customButton2"label="ConItalic"size="large"onAction="conItalicSub"imageMso="Italic" />
<button id="customButton3"label="ConUnderline"size="large"onAction="conUnderlineSub"imageMso="Underline" />
</group>
<group idMso="GroupEnterDataAlignment" />
<group idMso="GroupEnterDataNumber" />
<group idMso="GroupQuickFormatting" />
<group id="customGroup1"label="Push These">
<button id="bRunSub1"label="One"size="large"onAction="onMyButton"image="about" />
<button id="bRunSub2"label="Two"size="large"onAction="onMyButton"image="add_file" />
<button id="bRunSub3"label="Three"size="normal"onAction="onMyButton"image="add_folder" />
<button id="bRunSub4"label="Four"size="normal"onAction="onMyButton"image="application" />
<button id="bRunSub5"label="Five"size="normal"onAction="onMyButton"image="break" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
[/vba]

6. The customUI Editor menu buttons are

File Open (the XLSM)
File Save (the XLSM)
Add a icon (PNG files are best) to the 'container' for buttons, labels, etc.
Validate (XML is very fussy, spelling, UC/LC, etc.)
Generate CallBacks

imageMso= is to use a built in icon, and image= is to use one that you included (Add a icon) yourself.

7. You need the Callback to get the VBA that you want to past into a regular module. Each type of control has it's own calling format, including the Ribbon. Pasted this into the demo XLSM also

[vba]
'Callback for customButton1 onAction
Sub conBoldSub(control as IRibbonControl)
End Sub
'Callback for customButton2 onAction
Sub conItalicSub(control as IRibbonControl)
End Sub
'Callback for customButton3 onAction
Sub conUnderlineSub(control as IRibbonControl)
End Sub
'Callback for bRunSub1 onAction
Sub onMyButton(control as IRibbonControl)
End Sub
[/vba]

Each type of control has it's own features, but the MSDN has some good background reference material, as does

http://www.rondebruin.nl/ribbon.htm

and the refenence XLD gave you


Have fun

Paul