Adding a ribbon item...excel '07

[jproffer]

Latest problem...

Our machine at work was just updated to Excel '07. I have an add-in that our dept. uses ALL the time and it would be much more handy to have to it as it's own ribbon item instead of buried in the "add ins" box.

I know there is software available to add the "old style" menu above the ribbon but I doubt the company is going to spend (and I'm SURE not going to spend on their behalf) the, even little bit of, money to get it.

Also, it's not the end of the world if it's not possible to programmatically add a ribbon item, but if anyone knows a way...has the code...knows where I can even begin to look for this needle in the haystack....any help would be greatly appreciated.

Thanks in advance

[Bob Phillips]

It is not that hard, but you need to work with XML. Ken has the best intro IMO, read part 1 at http://excelguru.ca/blog/2006/11/20/...art-1-of-many/

[Paul_Hossler]

Also, check out the

Office 2007 Ribbon UI

Forum here in VBAX

Paul

[jproffer]

Yikes, lol

Looks pretty involved converting from the menu-maker I had in the previous version to the XML version I'm going to need now.

I'll have to read up on that later tonight...at first glance it looks like I'll have to completely rebuild the add-in...???...into a xlsm (or whatever it said..macro enabled workbook). Before I even begin to do this, I'll definatelty read up as much as possible.

Thank you for your help gentlemen

[Paul_Hossler]

Not really to hard to add some simple ribbon controls to an XLSM and then save it as an add-in

The basic XLM is "pretty' straight-forward for just adding buttons, etc. Some of the more advanced controls can be tricky, and I haven't tried some of the more "pro" stuff.

Hardest part for the stuff I ever did, was picking the right picture for the button.

Post any questions here.

Paul

[jproffer]

I can't even figure out where to start...lol.

In its present form, the menu is made from a looping code (below) that pulls from a worksheet within the add-in. (Quite an impressive piece of code...written by John Walkenbach).

I'm not even sure code started in previous version can have code in...XML, is it?

Finally, to top things off, I have excel XP at home, and obviously '07 at work, so anything I test has to wait until I'm there.

anyhow, ANY ideas to get me started to go from this:

[VBA]Sub CreateMenu()
' This sub should be executed when the workbook is opened.
' NOTE: There is no error handling in this subroutine

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId
Dim X As Long
On Error Resume Next
Dim counter As Long
counter = 1
''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
1 Set MenuSheet = Workbooks("Quality_Control_TRS.xla").Sheets("MenuSheet")
''''''''''''''''''''''''''''''''''''''''''''''''''''

' Make sure the menus aren't duplicated
Call DeleteMenu
' Variable for menu position
X = Application.CommandBars(1).Controls.count
' Set the main menu item position on menusheet cell C2
Workbooks("Quality_Control_TRS.xla").Worksheets("MenuSheet").Range("C2").Va lue = X + 1
' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
temporary:=True)
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem = MenuObject.Controls.add(Type:=msoControlPopup)
Else
Set MenuItem = MenuObject.Controls.add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True

Case 3 ' A SubMenu Item
Set SubMenuItem = MenuItem.Controls.add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
If counter = 1 Then
counter = counter + 1
GoTo 1
Else
End If
End Sub[/VBA]

to what I need would be greatly appreciated. I don't expect anyone to do it for me (although at this point I can't say I'd turn ya down ), but any help or push in the right direction would be great.

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

[Bob Phillips]

Have you opened the xlsm file in the Custom UI editor that Paul pointed you at?

[jproffer]

No sir...I'm going to try to DL that tomorrow at work. I guess I was getting ahead of myself trying to open it "normally".

I'm reading some of the links, etc posted tonight.

[Bob Phillips]

You can open the XML file separately, but you have to change the Excel file extension from xlsm to zip and open it with WinZip or some such program. You will see all of the files then.

[jproffer]

Just to keep you both, sorta, up to speed...

I'm at work now, I installed the UIEditor and it seems to work great.

I started to modify the code for the ribbon and all was...WAS going well.

I changed the main tab name...save, close, open in excel...worked fine...go back to the UIEditor.

Changed some of the button names...save, close, open in excel, looks good.

Added another group with about 25 buttons....save, close, open in excel and.........nothing.

The custom tab is gone. So now I'm going to DL your "really simple example" and try again, being careful not to change ANYTHING except the labels and id's and see what happens.

I can see a very small, very dim light at the end of a very long tunnel, lol. Thanks to both of you.

After I get the tab to "look" like I want it to, then I have to make it actually DO something. I'm hoping I can add this file as an add-in and change the "onAction" for each button to call the code from the original add-in...?????... c'mon, make me smile, tell me I can do that...lol.

Anyow, thanks again...at least now I can actually see what you've been talking about and it's starting to make sense.

[Bob Phillips]

Of course you can. As you can see, the XML has a n OnACtion property just as commandbars do.

[jproffer]

Roger that. It looked similar...I was just hoping that it WAS similar.

OK, another idea (which, naturally, brings about another question):

Most of the (old) level 2 menu items have several level 3 items attached to them. Making a group for each level 2 item would be fine, but then each group would have up to 25...sometimes 30 buttons. Near as I can figure this would make the ribbon approximately 24 "monitors wide" ...

So I was looking at the stock ribbon in excel and some of the buttons have a small arrow, for instance, on the Home Tab, the Paste button...drops down to various specialty pasting choices.

So I'm wondering if I can make a dropdown button for each level 2 menu item, with their respective level 3 menu items below....???

Any ideas...and any idea what this special button might be called when coded.

A normal button is just:

[vba]<button [/vba]

and then it goes into the ID and Label and what-not.

so this might be:

[vba]<dropbutton
'or
<dropdownbutton
[/vba]

and then even after that, how would I attach these sub menus to these "dropbuttons" or whatever they are called?

And again...broken record here, but I can't even express how much I appreciate both of your help.

[jproffer]

OK, I've been looking some more and you can't change "group" to anything else, it has to stay "group" which is the labels at the bottom of the ribbon (I know I'm not telling you anything you don't know, lol...just talking it out).

So in reality, I only need one "group" with several drop downs similar to the "conditional formatting" button on the Home tab, would be ideal.

One button would say "Area Calculators" with all of the actual calculators listed below...another for "Volume Calculators" with calc's below, and so on.

I guess there's really no question here, per se. Just getting all these thoughts down before I forget .

[Bob Phillips]

Why would you not have an Area Calculators group, and a Volume Calculators group, separately?