JD McLeod used his keyboard to write :
Thanks GS. Not that it matters, but why the change in Excel 2007? It seemed
so easy for us "novice" users to create custom menus in the earlier version.
It seems very much more complicated now. Anyway, I read where i will need to
download RibbonX in order to create the add-in menus i need. Will other
users of the add-in have to also download RibbonX or is that just for
developing the add-ins? Thanks.
RibbonX is part of Excel. You just need to download the viewer (Custom
UI Editor for MS Office) to allow you to edit the XML that is used to
modify the ribbon. I recommend you follow Dave's links. Bob Phillip's
article is one of the best I've seen on the web so far. Ron de Brun has
some good xlsm samples on his site as well. I don't use workbook-based
addins much anymore but yes, your addin will set up the ribbon for
anyone who uses it.
As for why M$ changed things goes, the answer is in their second
initial! It definitely complicates programming more than it needs to
be, though. That's partly why I stopped using workbook addins. Problem
I see here is that the next workbook opened that changes the ribbon
puts your addin in the back seat until it closes. I suspect Excel
toggles settings according to which workbook is active but that
scenario doesn't appeal to me at all. Most all my addins are
task-specific apps that I prefer to run as dictator apps in their own
instance of Excel. That way I can hide the default ribbon tabs and use
my own menus/toolbars by ensuring the Addins tab is the only one
available. This can be done with workbook addins too but many of my
clients are overly security cautious and so I use VB6 COMAddins.
Fortunately, this is the only case where the XML can be passed via
code. Otherwise, I'd need to use a dummy xlsm to set up my UI. Way too
much hooplah for me! Besides, the Addins tab supports using our earlier
version menus and toolbars so why try to fix what ain't broke? Most my
addins use a table-driven system for creating menus/toolbars and so I
use the same xla to do that and have all menus use a common procedure
(as in the sample in Bob's article) to pass the OnAction (in the Tag
prop) and any parameters (in the Parameter prop) to the COMAddin. Bob's
example uses the 'Run' method to execute his procs in the xla. My
COMAddin uses the 'CallByName' function to do the same thing,
respectively.
Yep, the ribbon sure has brought a lot of hassles into the picture.
Nice thing about my approach is 'no ribbon, no hassles'! I totally lock
down Excel so the user only has access to features and functionality
required to use my app. That completely removes any distractions caused
by the Excel UI that isn't being used. It also allows me to prevent
opening workbooks that don't belong to my app. Fact is, some of my
clients don't even realize they're using Excel!
Anyway, follow those links and you'll have results you can smile about.
regards,
Garry