Dynamic Flexible Toolbars (Word2000/Excel2000)

[ChrisGreaves]

With a lot of help from Fill-down for table cells and Docking a Dynamic toolbar I have cobbled together a prototype of a mechanism for building flexible toolbars on-the-fly.

Rationale:
(1) I get tired of Word corrupting my templates when I have painstakingly assembled toolbars with over 600 macros on a menu-tree (*)
(2) I like to see my toolbars grow as each set of macros is added during application development, without having to manually add toolbar items while I'm coding.
(3) I pretty well am forced to do it in Excel and I think PowerPoint anyway.

The attached file holds VBA code that was tested in Excel2000, exported to BAS/CLS, then imported into Word2000, so it has a degree of mobility. (see below)
The body of the document has an example of the code I generate semi-automatically; I use a utility to list all the procedures in a project, sort away the debris and collect just the macros in a table, place the VBA code around and Fill-Down the constant stuff, then paste the VBA code into a module.

We are only a few weeks away from my annual Thank You to the lounge, but what's wrong with saying an extra one?
THANKS YOU to all those who push me forwards and give me insights into VBA and the MSOffice desktop applications.
You know who you are, and I'm glad to know you.

(*) I almost forgot to mention that the trailing parameters of the calls to the procedure "AddMacroButton" can be a string of sub-sub-sub menus; that is, you can define a real tree structure, not just a 1-level menu system.
See for example

Code: Select all

Call AddMacroButton("AutoFitAllcolumnsInWorkbook", "", lngFaceId, oToolbar, "Formatting", "Columns")
Call AddMacroButton("AutoFitAllcolumnsInWorksheet", "", lngFaceId, oToolbar, "Formatting", "Columns")
Call AddMacroButton("AutoFitAllRowsInWorkbook", "", lngFaceId, oToolbar, "Formatting", "Rows")
Call AddMacroButton("AutoFitAllRowsInWorksheet", "", lngFaceId, oToolbar, "Formatting", "Rows")

Below: The Jan Karel hint about adding ThisWorkbook.Name! for Excel needs to be removed in the Word2000 version; ThisDocument.Name! brings up "This macro cannot be found ..."

[HansV]

Thanks, creating custom toolbars using code is a very useful technique.

Minor quibble: I don't really like the idea of automatically assigning a FaceID to the buttons - it results in button icons that are completely unrelated to the function performed by the button. I'd either assign meaningful icons or do without.

[ChrisGreaves]

Minor quibble: I don't really like the idea of automatically assigning a FaceID to the buttons

Hans, that's not a minor quibble, it's a MAJOR quibble, because you are absolutely correct.
Like you, I like meaningful icons.
The dynamic toolbars is still a WIP.
I wanted to provide icon-capability, which is why I set up a cheap'n'nasty way of seeing that different icons really did land on the buttons (lngIcon=lngIcon+1 or similar), and I left in place

Code: Select all

        .Style = msoButtonIconAndCaption
        ' .Style = msoButtonCaption ' I'd either assign meaningful icons or do without.

as a crude indicator that one could do without the icons.
I woke up this morning thinking that besides the literal line-by-600-line calls to "AddMacroButton" it would make sense to have a table-driven version, so that I didn't spend time doing the old Fill-down trick followed by conversion of the table to text prior to pasting into a module.

[HansV]

...which is why I set up a cheap'n'nasty way of seeing that different icons really did land on the buttons.

OK, I suspected that was the reason. I pointed it out mainly as a warning to others trying out your code.