XL 2010 Ribbon Modifications

[kgkev]

I am new to 2010.

In the past I have always had macro to open and move around my most popular spreadsheets,

these macro still work but I am having trouble changing the Ribbons into a nice format.

I have downloaded the customUIeditior and have managed to create / edit some ribbons attached to sheets but I want to be able to edit the ribbons on personal.xlsb


If I open personal.xlsb in customUIeditor it doesn't show anything?

Any ideas?

 

[dantheman9]

hi kgkev,

I have used custom UI editor, but only to make excel 07 ribbon buttons - having said that, it still seems to work;
try the sample code below to get the ribbon interface working;
from custom UI open the excel worksheet then some of the following will help

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]<[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]?xml [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]version[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]1.0[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]encoding[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]utf-8[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] ?> 
<[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]customUI [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]xmlns[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]http://schemas.microsoft.com/office/2006/01/customui[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] > <[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]ribbon[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]> 
<[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]tabs[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]> 
<[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]tab [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]id[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]mytab[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]label[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"NAME OF LABEL ON RIBBON BAR[/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]> 
<[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]group [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]id[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]group[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]label[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"SUB GROUP LABEL NAME[/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]> 
<[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]button [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]id[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]myButton[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]label[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"OPEN FORM [/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]image[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"pic1[/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]size[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]large[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]onAction[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]myButton_ClickHandler[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] /> 
<[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]button [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]id[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]mybutton1[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]label[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"CLOSE FORM[/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]image[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"pic2[/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]size[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]large[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]onAction[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]myButton_ClickHandler2[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] />
</[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]group[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]>
</[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]tab[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]> 
</[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]tabs[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]> 
</[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]ribbon[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]>
</[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#800000][SIZE=2][COLOR=#800000]customUI[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]>
[/COLOR][/SIZE][/COLOR][/SIZE]

this will form a new tab ribbon with one local open to open and close a form via the 'myButton_ClickHandler"

each button can have a image icon, you need to open them into custom UI first, then you can link to them via the image option within the button tag (sampled as pic1 and pic2 in the code above).


then you can program the buttons from vba:

Sub myButton_ClickHandler(control As IRibbonControl)
Userform1.Show
End Sub
 
Sub [COLOR=#0000ff]myButton_ClickHandler2([/COLOR][COLOR=#000000]control As IRibbonControl)[/COLOR]
Userform1.Hide
End Sub

Hoope that helps?

 

[kgkev]

Whats my best option then?

Should I save the ribbon to the personal file and then edit it?

If this is the best option how do I get the ribbon saved in the personal file?

 

[dantheman9]

if you want the ribbon to be accesable for all uses of excel, edit one workbook with custom UI, then from excel save it as an addin, load that into excel an you should be done

 

[RoryA]

I'd start again using the CustomUI editor to edit the .xlsb file. I don't know of a way to import from the existing setup and it doesn't look like a simple copy and paste will work.