Sorry, I may have answered this part of the question before, but might as
well answer it here:
Where/How the toolbars get activated in my usage of the Module Toolbars, the
code to which has been provided earlier in this thread:
In ThisWorkbook:
I have Workbook_BeforeClose, Workbook_Open, Workbook_WindowActivate, and
Workbook_WindowDeactivate sub-routines. I do nothing special with these
sub-routines other than call particular routines within the Toolbars module.
For Open and activate, I call ToolBars.All_Bars and for the reverse I call
All_Bars_Delete, although I have found one instance where I do not delete the
bars in the WindowDeactivate for one of the programs I use a version of the
toolbars.
If I want the or a toolbar to appear only on one sheet and not others, then
the code for that worksheet has Code in the Activate and Deactivate
sub-routines. You could use the All_Bars_Hide on the Activate, and then Show
only the toolbar(s) that you want. For the Deactivate, I use the
All_Bars_Hide. Of course if this is properly done for all worksheets that
will use a toolbar, then really the activate must only contain the code to
show the toolbar(s) desired, and the deactivate will clean-up by hiding all
bars. You could say I have a little redundancy in the event that I as the
programmer have forgotten to hide all the toolbars when leaving some other
sheet.
However, with either route, if I go to another worksheet all the toolbars
are now hidden, whether I need a toolbar on the next sheet or not, and that
new sheet will "provide" the necessary toolbars.
Yes the example I provided, returns the name of the first toolbar
(TbrName(0)) and will provide a blank for TbrName(1). I did this also in the
event that I wanted to cycle through each toolbar and do something with it.
By placing a "blank" at the "end" of the toolbar list, I could determine that
I had reached the last toolbar, without having to remember to change the
value of some variable in the code. I.e., global variable NUMTOOLBARS = 1,
and then when I add a new toolbar having to remember to update that global
variable to reflect the value of 2... I don't think I wrote a helper
function to return the number of toolbars in place, but certainly the ability
is there... (I.e., function NumToolBars() as integer; NumToolBars = 0; do
while tbrName(NumToolbars) <> ""; NumToolBars = NumToolBars + 1; loop)
Now, I call All_Bars first, this creates each toolbar (as programmed, and in
this case is only Tool_Bar1_Create) then activates a particular sheet.
Activation of the sheet ensures that whatever activate code is associated
with that sheet, the appropriate toolbar appears. In my case, when I
originally designed the toolbars, I had a single source data sheet, and
several other sheets that used data from that source. So it made sense that
when opening the workbook, that sheet1 was activated. Sheet1 refers to the
VBA name of the sheet, not the name provided on the tab at the bottom of the
EXCEL name. That would be Worksheets("Sheet1") in a newly created workbook.
And as the individual responsible for the code that was going into the
program, and not controlling the users ability to change the name onthe tab,
I was able to use Sheet1. And if Sheet1 didn't exist, I could always
programmatically create it, and if need be, change the VBA name for it from
whatever Sheet# Excel returned to Sheet1.
I think that after this short little training course of my last two
postings, that you may well at least be able to implement your own toolbar.
I said that the .FACEID was something I could readily obtain. Well it's not
quite as readily available as one might think, and I can't recall if I have
implemented a good way of determining it. But I have done something
interesting at one point. What I did was use the TOOLBARS code, to create
like three buttons on a menu. Then I used a user form, with a scroll option
to increase or decrease my starting .FACEID. The .FACEID was shown on the
user form. Then I would increment or decrement the faceid. After each
change of the faceid, I would delete and create the toolbar with that series
of faceid. When I found an icon that I liked, I documented the appropriate
faceid so that I could recreate it in my final usage.
Excel has some "default" faces that are shown by customizing toolbars, and
changing the icon. If you implement/choose one of those you can then go a
reverse route and have excel tell you the faceid applied. I think one time,
what I did was record a macro to create/modify an icon, and used that code to
determine the faceid that was created... Though I'm not 100% sure that that
information is provided when recording a macro and performing those actions.
At any rate, you should be able to modify/add a little bit of code,
implement a userform, and be able to discover some great looking icons to
associate with the task at hand. What I would do, would be to implement say
three buttons on a toolbar, in the Toolbar1 (or 2 or 3 or whatever)
properties subroutine, and require it to receive a value as the first value.
Then each successive icon faceid be equal to the previous plus one.
Obviously on your form you would be showing the current faceid number, and
with an increase, say have it increment as a value of 3, so first 0, then 3,
then 6, then 9. And depending on which icon in the group of three you like,
you know the faceid number that you are using.
How's that?