Excel 2007 menus - is this right?

[michael.beckinsale]

Hi all,

I have developed several workbooks for clients that have specific
built in menus according to the client / workbook being used and l am
anxious to continue providing that facility when they upgrade to Excel
2007

I would appreciate it if the contributors to this site could confirm
that l have understood the information l have read correctly. My
understanding so far is as follows:

1) If l do nothing the menu item will appear in the add-in group of
the ribbon.
2) Using the RibbonXL Editor available as a download from Microsoft
you can:

a) write XML script to add groups / menu items to the ribbon
b) using the RibbonXL Editor built in functions to generate
Callbacks to VBA
c) write XML script to delete the groups you have added to the
ribbon?

Assuming my understanding is correct l have the following questions:

1) is the XML script is "attached" to the workbook, therefore when the
workbook is saved, closed and re-opened the groups will be added to
the ribbon.
2) the above is not dependent on the user having the RibbonXL download
present? Therefore the workbook can be distributed and will still
display the customised menu.
3) can the XML script be accessed via the workbook? In previous
versions of XL l assume this was possible via the script editor which
does not seem to be available in XL2007.
4) what exactly are XML callbacks? I originally assumed that they were
commands in XML that invoked VBA macros (not exclusively). Having
read / looked at Ron De Bruins examples l am not so sure.

All help carification greatly appreciated.

I am happy to keep this group informed about my findings / discoveries
as l am sure that l am not the only one who is keen to be able to
continue customising the menu .......now the ribbon

Regards

Michael Beckinsale

 

[Ron de Bruin]

Hi Michael

1 =yes
2=yes

Assuming my understanding is correct l have the following questions:

1=yes
2=yes
3= yes, you must change the extension to .zip and then you can edit the xml.
Using the UI editor is much easier

4= if you use onaction in the xml to run a macro and press the generate callbacks button in the UI editor
it will create the sub for you.
Example for onAction="Macro1"

'Callback for customButton1 onAction
Sub Macro1(control as IRibbonControl)
End Sub

You copy the callbacks in a module in your workbook and add your code

Try the example on my ribbon page
http://www.rondebruin.nl/ribbon.htm

 

[michael.beckinsale]

Ron,

Many thanks for the reply.

I'm glad l understand correctly. Its not very easy or user friendly is
it!

Would you be kind enough to provide a bit of further information:

1) Can you tell me how you remove the menu item / group from the
ribbon (and provide the code?)

2) I, like yourself, use a variation of J Walkenbach's menu maker to
generate the custom menu. I wondered if it is posible to combine the
XML code & JW's VBA code to generate a custom menu that appears on the
ribbon. I have seen your approach of adding a menu to the QAT by this
is not very practical when distributing workbooks to clients, and it
requires their manual intervention.

3) Cheeky l know but if 2) can be done can you provide code / point me
in the right direction?

I will continue to "play around" and if l can add anything worthwhile
to your findings l will let you know.

TIA

Regards

Michael Beckinsale

 

[Ron de Bruin]

Hi Michael

1) No need to do this, when you close or deactivate the workbook the ribbon menu or group is deleted from the ribbon.
With commandbars you must create menus and also use another macro to delete them.

2) Yes that is possible .
You can create the menu like I do in the popup menu examples on my site and add one button on the ribbon with xml that
call the popup macro

ribbon. I have seen your approach of adding a menu to the QAT by this
is not very practical when distributing workbooks to clients, and it
requires their manual intervention.

If you add the menu to the QAT it is there when the user open the workbook.
The QAT button is saved with the file

3) Add this code in the callback of the macro you assign with onaction

On Error Resume Next
Application.CommandBars(ThisWorkbook.Sheets("MenuSheet").Range("B2").Value).ShowPopup
On Error GoTo 0

I will explain more on my site soon (will update it this evening )

Thanks for the feedback

 

[michael.beckinsale]

Ron,

Again thanks for the info.

I will play around with it later if l get a chance.

Regards

Michael Beckinsale

 

[Ron de Bruin]

Hi Michael

I make a few changes and I hope it is more clear now ?
http://www.rondebruin.nl/ribbon.htm

Please let me know if you think