Excel 2007 Menus - Ron De Bruin please

  • Thread starter michael.beckinsale
  • Start date
R

Ron de Bruin

Hi Jim

First test as Add-in : I see the button but it will not run the code.
If you choose customize QAT you not see it in the icons list in the "For all documents list"
 
R

Ron de Bruin

hi Jim

Stupid from me

I not commented the workbook name test in the code (It is late)
If ActiveWorkbook.Name = ThisWorkbook.Name Then

But it is working OK as far as I can see
 
M

michael.beckinsale

Ron / Jim,

You learn something everyday. I have never heard of a 'oxymoron' so
just used the word script as it seemed to fit. My apologies.

Jim perhaps l didnt make myself very clear but l think you are
agreeing with me anyway.Perhaps this is clearer:

When l develop a workbook for distribution to end users l want to
avoid them having to add to the QAT (which MS is discouraging), even
if it only needs to be done once. I want the workbook to automatically
create a new tab, group & buttons. On closing the customised tab will
be removed.

My choices are:

A) Full XL Ribbon method - Use XML & callbacks to generate the custom
tab, group & buttons / dropdowns within the group. Each tab, group,
button & dropdown item has to be defined in XML. The appropriate code
is then written in the VBE using the callbacks generated by the Custom
UI Editor. The downside being the amount of effort required by the
developer who may need to learn XML. The benefits being that the end
user uses the same number of clicks to access the macros as in
previous versions of XL.

B) Partial XL Ribbon method - Use the minimum number of XML lines to
create a custom tab, group & button. Use the button callback to invoke
Rons 'MenuMaker'. The benefits of this approach are that most of the
menu building can then be done within the workbook on the 'MenuSheet'
and the resulting popup will be familiar to the end user if upgrading
from previous versions of XL. Downside is that the user will have to
use 1 more mouse click than previously.

For personal use l have created 'MyNewBlankWB-Menu.xlst' which
incorporates Rons 'MenuMaker' so that l can easily develop &
distribute workbooks using method B). I only have to rename the tab,
group & button in XML then add my macro's to the 'MenuSheet' page and
l have a customised XL Ribbon with my defined tab, group & button to
generate the popup.

I am still toying with the idea of a XL.xlst for method A) but cant
decide if its worth the effort as it will probably still need
considerable editing in practice, and Ron's examples cover most
eventualities.

Sorry if its a bit long winded

Regards

Michael Beckinsale.
 
R

Ron de Bruin

When l develop a workbook for distribution to end users l want to
avoid them having to add to the QAT (which MS is discouraging), even
if it only needs to be done once. I want the workbook to automatically
create a new tab, group & buttons. On closing the customised tab will
be removed.

If you use the "one workbook" example the user not have to add the button to the QAT when you send your workbook
You not have to use xml then

I am still toying with the idea of a XL.xlst for method A) but cant
decide if its worth the effort as it will probably still need
considerable editing in practice, and Ron's examples cover most
eventualities.

I am working on this Michael
 
J

Jim Rech

Ok, Michael, I follow you.<g>

I suppose you could add a choice C - just use Commandbars as in Excel 2003
and direct the user to find your menus on the Add-ins tab. Not terribly
attractive other than it's very easy.

The thing I liked about Ron's technique is that it provided a way to
seemingly add a dropdown to the QAT, which is something that I do not think
is possible through straight RibbonX. Of course you're not really adding a
dropdown to the QAT; you're just adding a button that runs a macro that pops
up a menu, but that's splitting hairs.

I must admit I never considered doing the same thing on the ribbon proper,
because RibbonX does support adding menus there...

Ok, now I've considered it. And I don't really care for it. For one thing
it doesn't embrace the ribbon, it just co-exists with it. You apps wouldn't
have the new look, and they wouldn't be able to use the features of the
ribbon that are not possible through Commandbars like gallery controls,
super tooltips, etc.

Now, you've said that one of the downsides of "A", the full XML ribbon
method, is having to learn XML. Is that really a bad thing? Haven't you
always sort of wanted to but never had a good reason to? Now you do<g>.
The ribbon isn't going away any time soon. Are you going to dodge it the
rest of your professional life? It's really a subset called RibbonX you'd
learn anyway, you don't have to understand all the intricacies of XML proper
by any means.

And guess what, it's not that hard. Once you get the hang of it (and an XML
editor) it's pretty easy!

--
Jim
| Ron / Jim,
|
| You learn something everyday. I have never heard of a 'oxymoron' so
| just used the word script as it seemed to fit. My apologies.
|
| Jim perhaps l didnt make myself very clear but l think you are
| agreeing with me anyway.Perhaps this is clearer:
|
| When l develop a workbook for distribution to end users l want to
| avoid them having to add to the QAT (which MS is discouraging), even
| if it only needs to be done once. I want the workbook to automatically
| create a new tab, group & buttons. On closing the customised tab will
| be removed.
|
| My choices are:
|
| A) Full XL Ribbon method - Use XML & callbacks to generate the custom
| tab, group & buttons / dropdowns within the group. Each tab, group,
| button & dropdown item has to be defined in XML. The appropriate code
| is then written in the VBE using the callbacks generated by the Custom
| UI Editor. The downside being the amount of effort required by the
| developer who may need to learn XML. The benefits being that the end
| user uses the same number of clicks to access the macros as in
| previous versions of XL.
|
| B) Partial XL Ribbon method - Use the minimum number of XML lines to
| create a custom tab, group & button. Use the button callback to invoke
| Rons 'MenuMaker'. The benefits of this approach are that most of the
| menu building can then be done within the workbook on the 'MenuSheet'
| and the resulting popup will be familiar to the end user if upgrading
| from previous versions of XL. Downside is that the user will have to
| use 1 more mouse click than previously.
|
| For personal use l have created 'MyNewBlankWB-Menu.xlst' which
| incorporates Rons 'MenuMaker' so that l can easily develop &
| distribute workbooks using method B). I only have to rename the tab,
| group & button in XML then add my macro's to the 'MenuSheet' page and
| l have a customised XL Ribbon with my defined tab, group & button to
| generate the popup.
|
| I am still toying with the idea of a XL.xlst for method A) but cant
| decide if its worth the effort as it will probably still need
| considerable editing in practice, and Ron's examples cover most
| eventualities.
|
| Sorry if its a bit long winded
|
| Regards
|
| Michael Beckinsale.
|
|
 
M

michael.beckinsale

Jim,

I agree with you entirely, and as you say it isn't really that hard
once you have got the right tools.

If l have to develop a workbook from scratch specifically for XL2007 l
will certainly use the 'Full Ribbon' method.

I now have choice of methods to upgrade workbooks to XL2007.

I have found this thread to be both informative & educational.

Many thanks to all that have contributed.

Regards

Michael Beckinsale
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top