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

[jproffer]

yowza

there's 3 more days of reading. Thanks so much for that. Like i said, I have to do all this at work, and I go back on friday so...I'll (hopefully) be able to DL the UIEditor there, and (hopefully) be able to try some of this at that time.

Thanks again, Paul

[Paul_Hossler]

Boy, you're fast -- I wanted to double check the sample file, so I edited the earlier reply to add the file and you picked it up before I saved the edits

Paul

[Paul_Hossler]

Also highly recommended -- both the complete books and the samples

Excel 2007 VBA Programmer's Reference
Written by John Green, Stephen Bullen, Rob Bovey and Michael Alexander

http://www.oaltd.co.uk/Excel2007ProgRef/Default.htm

http://www.oaltd.co.uk/DLCount/DLCou...ogRef_ch14.pdf


RibbonX: Customizing the Office 2007 Ribbon (Paperback)

by Teresa Hennig (Author), Robert Martin (Author), Ken Puls (Author)


http://www.excelguru.ca/blog/2008/01...e-2007-ribbon/

http://www.excelguru.ca/node/93


Paul

[jproffer]

I'm not that fast. I guess the forum is set to auto-subscribe to any thread I start or reply to...which sends an email...and that email is a hotmail account which is linked to MSN messenger...which is always on, lol.

Anyhow, I hadn't copied anything from any of the replies yet. With all the links, I have enough reading for a week...which is good. I genuinely want to figure out how to do this.

Thanks again...

HAPPY NEW YEAR!!!


Tomorrow when I get back to work, hopefully I can start the first of many...MANY rounds of testing.

[jproffer]

The "really (so called) simple example" file you have just tells me that I'm completely...COMPLETELY over my head.

I can't even FIND the code that makes the ribbon addition when the WB opens, much less modify it to suit what I want(ed) to do.

I will admit that I haven't had a chance to read all the references yet...I will, and maybe that will point me in the right direction...but as it stands right now, our add-in is going to have to rest it's weary bones in the "Add-Ins" ribbon area.

As I said, I do fully intend to look into all of the links you posted and I appreciate you taking the time.

I can see now, that this isn't going to be a "come in one morning and have this done and ready to go in an hour or two" type of job.