excel 2007 add custom menu

R

Ron de Bruin

Hi ILearner

Creating a Popup menu with the old code is the only way in Excel 2007
Or you can change the ribbon

Look for examples in the 2007 section of my site
 
I

ILearner

Found it and got it working.
Crazy, the world turned upside down, you to supply free workarounds for
removed microsoft functionality.

How many menu's next to each other can there be created?
How can a workbook be assigned to be opened? Workbook.open() in a macro?

Sorry, but even as a workaround I need to decide if this is what I want.

I read some comment from somebody else and I agree: removing functionality
is destructive for Microsoft. Maybe I will learn how to work with macro's in
Open Office.

Thanks anyway.
 
J

Jon Peltier

Crazy, the world turned upside down, you to supply free workarounds for
I agree, and I can only hope that Microsoft will take such criticisms and
suggestions to heart.
the ribbon is not so bad as you think.
Click to expand...

Not as bad, perhaps, but I still greatly dislike it. I'm learning to deal
with it, but the entire interface leads to lower productivity. Even the
simplest tasks require more mouse movement and clicking than in earlier
versions of Excel. I also don't like the heavy-handed manner in which the
Office team thrust it upon us.

- Jon
 
N

Nick Hodge

Jon

I agree it has some features which are a 'backward' step (tearing off to put
near work, being the worsed), but the more I work with it the better I find
the customisation story. Apart from the 'camelCase' in the xml, which takes
a little longer to get used to and little quirks like dialogBoxLauncher
needing to be declared last in a group, I find it far better and more
forgiving in a strange way. That is you either get the ribbon look you want
or nothing. Somehow it's much nicer than shouting at a syntax dialog!

I was stumped with dynamic stuff, but am now starting on that road.

Granted, for casual users, they really only have the QAT to customise, maybe
a tear-off QAT would help them.

I really like the feature of dragging custom controls onto the QAT and they
just work!

On usability, the quickest way around Excel is keyboard and *most* of the
old keyboard settings still work.

Lastly, to just not load the ribbonX and the UI is exactly as it was is a
major step forward. The number of 'stray' custom toolbars I've seen is
without number. XL4 had two toolbars, 2003 had 32! Something had to change
and lets hope this is V1, but IMVHO, it ain't as bad as some think...just
very different. (my £0.02)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk
 
H

Harlan Grove

Nick Hodge said:
Granted, for casual users, they really only have the QAT to
customise, maybe a tear-off QAT would help them.
Click to expand...

Or maybe allow multiple QATs and FTSUHOI call them ToolBars or
CommandBars.
I really like the feature of dragging custom controls onto the QAT
and they just work!
Click to expand...

As opposed to dragging built-in commands to the old command bars in
Excel 97 through 2003? What didn't work for you?
On usability, the quickest way around Excel is keyboard and *most*
of the old keyboard settings still work.
Click to expand...
....

Excel 2007 even includes Lotus transition navigation keys even though
it can no longer open Lotus files. Consistency of vision wasn't high
on the Excel development team's priority list.
. . . XL4 had two toolbars, 2003 had 32! Something had to change
Click to expand...
....

Why?

Many of those toolbars only appeared in specific contexts. And one
could let them float or dock them where one preferred as opposed to
the new regime in which they're all stuffed into the ribbon or the
QAT.

There's too much choice in English spelling, and since there are more
American English speakers, why not let the US DOE promulgate how
English words should be spelled in the UK? Our spellings are generally
shorter, so more efficient.

Is there no value to choice or variety?

The ribbon as a new menu is one thing. XL5 got a new menu (though it
included an alternative XL4 menu as an option), so XL12 getting a new
menu (ribbon set to autocollapse) is a pain, but nothing new.

Now the fact that the file and printing commands aren't in the ribbon
but in the oversized Office icon is a major inconsistency with no
apparent logical explanation, and the fact that the new settings
dialog involves scrolling is a huge step BACKWARDS in usability are
mere warts on the V1.0 visage.

But the elimination of floating toolbars/toolbars docked on the left,
right or bottom sides of the application window as the user sees fit
is an outright reduction in functionality. The Big Brother Knows Best
design paradigm.
 
H

Harlan Grove

Nick Hodge said:
IYHO
Click to expand...

The lack of ability to park any toolbar-like sets of icons on left,
right or bottom sides of the application window is an objective and
verifiable fact. No opinion involved. It's possible to create modeless
user forms containing objects that look like toolbar icons of old, so
rough equivalents for floating toolbars could be hacked but without
drag & drop customization.

This constitutes reduction in choice as that word is usually defined.

Only things that are matters of opinion are whether this is good or
bad and whether the old grab bag of toolabrs constituted welcome
variety or confusing bloat.
 
N

Nick Hodge

Harlan

Agreed, I just feel that many of the features on old toolbars were not
discoverable, there's many I've not found/used surely. Now whether they're
more discoverable now is questionable?? For the average to power user, it's
probably a backward step and MS 'claim' to have used data collected from the
'User Experience' program, which most experienced people 'turn off'

I've actually turned mine back on so I can moan a little more next time ;-)

I think another issue is that so much of this is non-application specific,
for example the ribbon is now 'owned' by another group. The officeMenu
(RibbonX element) is owned by the UI team across apps and even the charting
(and shapes) is partly controlled by an OfficeArt team. This explains a
reasonable amount of the inconsistencies and I believe the application teams
need more input but that could also cause decisions 'by committee which is
often bad.

Do I like it more...yes, definitely. I can get customisations done quickly
and 'safely' using ribbonX and VBA and unload them simply. (Take the
dictator app from the other day...no issue in 2007) and being a *totally*
non-design guy, (you should see how I dress), I can get great looking
documents done quickly and looking great. I could get them done quickly
before, but....

Again... MVHO and £0.02

--
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk
 
H

Harlan Grove

Nick Hodge said:
. . . I just feel that many of the features on old toolbars were
not discoverable, there's many I've not found/used surely. Now
whether they're more discoverable now is questionable?? For the
average to power user, it's probably a backward step . . .
Click to expand...

One big example: I have the Forms toolbar docked on the right hand
side of my Excel 2003 window and the Drawing toolbar docked on the
bottom of it. Not trivial at all to get both on screen at the same
time in Excel 2007.

Now there were/are some thoroughly pointless built-in toolbars, such
as the Borders toolbar, that's a much, much bigger PITA to use that
the Border tab in the Format Cells dialog. Getting rid of them is
definitely a good thing. Unfortunately, MSFT got rid of the useful
ones too.
. . . and MS 'claim' to have used data collected from the 'User
Experience' program, which most experienced people 'turn off'
Click to expand...
....

I believe they did, but I also believe the people who opted in were
disproportionately nonbusiness users using few if any of the more
important business features. How many home users are fetching data
from ODBC data sources? How many are running pivot table reports on
OLAP cubes? How many are using more than 20 different worksheet
functions in their home budgets? On the flip side, how many business
users are allowed to use bandwidth to participate in the program?
for example the ribbon is now 'owned' by another group. The
officeMenu (RibbonX element) is owned by the UI team across apps
and even the charting (and shapes) is partly controlled by an
OfficeArt team. . . .
Click to expand...
....

Nothing new. Excel has suffered from ever closer integration since
Excel 5 when the first major menu overhaul happened.
. . . (Take the dictator app from the other day...no issue in >2007) . . .
Click to expand...

Really? It's possible for the ribbon not to appear at all? That means
not even the row of tabs.

It's possible to hide the QAT and the Office logo which gives access
to file system/printer commands and Excel settings?

BTW, how does one leave the ribbon, QAT and Office logo in place but
disable file system/printer commands as was possible in earlier
versions by disabling but not hiding specific menu items? That is,
does RibbonX allow modification of the Office logo menu?
 
N

Nick Hodge

Harlan

Comments in line

Harlan Grove said:
One big example: I have the Forms toolbar docked on the right hand
side of my Excel 2003 window and the Drawing toolbar docked on the
bottom of it. Not trivial at all to get both on screen at the same
time in Excel 2007.
Click to expand...

Harlan, I agree this one, there is no way I know of to move stuff around the
application frame or place near your area of work, e.g tear off
Now there were/are some thoroughly pointless built-in toolbars, such
as the Borders toolbar, that's a much, much bigger PITA to use that
the Border tab in the Format Cells dialog. Getting rid of them is
definitely a good thing. Unfortunately, MSFT got rid of the useful
ones too.
Click to expand...

That has to be IYO, as some users... not me, will use that feature
frequently. What's one man's PITA is another man's nirvana. (That's MSFT's
with there Watson SQM data)
...

I believe they did, but I also believe the people who opted in were
disproportionately nonbusiness users using few if any of the more
important business features. How many home users are fetching data
from ODBC data sources? How many are running pivot table reports on
OLAP cubes? How many are using more than 20 different worksheet
functions in their home budgets? On the flip side, how many business
users are allowed to use bandwidth to participate in the program?
Click to expand...

That's my contention too, hence I've turned mine on, but I'd agree that many
corporates would not allow by policy, connections of this type. They need to
find a better way of getting this information from a broader group *pre*
beta, as by that stage, particularly with the 'ownership' issue, it's often
too late and 'minds are set'. Not that Companies like Boeing, Ford, etc are
not helping to drive, but they have a very narrow view and one policy per
Company I concede.
...

Nothing new. Excel has suffered from ever closer integration since
Excel 5 when the first major menu overhaul happened.
Click to expand...

And I see that going even further with more of a Worksesque type 'shell'
Really? It's possible for the ribbon not to appear at all? That means
not even the row of tabs.
Click to expand...

Sure (Jim Rech's)

Sub RemoveRibbon()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub

(I know you'll laugh at ExecuteExcel4Macro...I can hear you ;-))
It's possible to hide the QAT and the Office logo which gives access
to file system/printer commands and Excel settings?
Click to expand...

See above
BTW, how does one leave the ribbon, QAT and Office logo in place but
disable file system/printer commands as was possible in earlier
versions by disabling but not hiding specific menu items? That is,
does RibbonX allow modification of the Office logo menu?
Click to expand...

Sure, you can, the first disables the save menu and the second re-purposes
the print button

<commands>
<command idMso="FileSave" enabled="false"/>
<command idMso="FilePrint" onAction="myPrint"/>
</commands>

And this will add an item to a menu on the officeMenu

<officeMenu>
<menu idMso="FileSendMenu">
<button id="button1" imageMso="FileOpen" label="Open"
description="Open Something" onAction="OpenMe"/>
</menu>
</officeMenu>


The following will leave you with a blank canvas and just the 'crucial
officeMenu operations. These can be disabled or re-purposed. (See below
ribbonX for one drawback to date)

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
</customUI>

The really unfortunate bit currently is you cannot remove, or probably more
usefully replace the office button icon, or individually remove the Excel
options or Exit Excel buttons. But then I think we all know this is very V1
and someone has to work with it or we can't suggests amendments in the real
world and staying still was not an option ;-)

I found this link, (three parts) really useful in my limited training so far

http://msdn2.microsoft.com/en-us/library/aa338202.aspx

I have a developing area on my site

http://msdn2.microsoft.com/en-us/library/aa338202.aspx

and Ron de Bruin's is probably more complete

http://www.rondebruin.nl/ribbon.htm

I know you and many others need a lot more convincing, but we also can try
and adopt and be constructive in our criticism and become influencers in
future changes, bearing in mind MSFT are NOT going to return to the old
commandbars model (I laughed at the launch that Application.StatusBar, has
become Application.CommandBars("Status Bar")!).

Try it and let's get some dialog going to MS on how to make it better, it
certainly needs that, I agree, but again IMVHO...I like the direction.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk
 
N

Nick Hodge

H

Harlan Grove

Nick Hodge said:
And I see that going even further with more of a Worksesque type 'shell'
Click to expand...
....

Why not resurrect Bob but give it office rather than home furniture?
Sure (Jim Rech's)

Sub RemoveRibbon()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub

(I know you'll laugh at ExecuteExcel4Macro...I can hear you ;-))
Click to expand...
....

Not at all. ExecuteExcel4Macro is the only way to make Excel/VBA pull
anything from closed workbooks using dynamic external references other
than entering them as cell formulas in hidden ad hoc workbooks.

Guess I need to refresh my XLM skills.
The really unfortunate bit currently is you cannot remove, or probably more
usefully replace the office button icon, or individually remove the Excel
options or Exit Excel buttons. But then I think we all know this is very V1
and someone has to work with it or we can't suggests amendments in the real
world and staying still was not an option ;-)
Click to expand...
....

Too V1.

Where I work, IT upgraded everyone who got new PCs in 2005 from Office
97 to Office 2002, and last year upgraded everyone from either Office
97 or Office 2002 to Office 2003. They never installed Office 2000.
It's highly unlikely they'll ever install Office 2007. Maybe we'll get
Office 14 a year or two after it's released. I'll be ready when it
happens, but I'm not exactly burning with anticipation.

As for home use, I still see no compelling reason to upgrade from
Office 2000, and even so I use OpenOffice. Since I can log onto the
company terminal server farm using either Windows of Linux versions of
the Citrix client, I can work from home using my home machine without
needing to buy any software I don't have any use for personally.

But that doesn't mean I don't have opinions.
 
Top