Sorry, I may have answered this part of the question before, but might as
well answer it he

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? :)



"davegb" wrote:

GB wrote:
Okay, item by item....

Function Name

This is a Function that is called Name (Not sure of your level of
experience, so want to start with basics.)

I figured out that this part was confusing me because there's also a
property called name. So I went through the module and changed your
function to TbrName so I could follow it better.


It is a public function, so that other modules can get the name of the
toolbar that is set to value 0, 1, 2, etc..

Value, is the toolbar number that I have designated to a particular toolbar.

In my case, I really just have 1 toolbar implemented, so the Select Case
Value isn't necessary. However, I program for expandability. I saw that I
might need additional toolbars in a single document, and wanted to have that
ability to expand.
So, if I want to get the name of Toolbar number 2 I call Name(2) and the
select case function gives me the name back of toolbar 2.
Ok, but in the code the name for toolbar 1 is "First Tool Bar", isn't
it? And I'm guessing that since there is no toolbar 2, it returns a
blank with it's present form. Is this correct?


For the application included, look for where Name(0) is used... This is one
way to back track and see how the Name() function is used and get an idea of
it's "importance/impact."

Ok, I've done that. It's a little clearer now.


As far as first run code.... The function called Name may be the first in
the list, but it is not the meat of the program. For the toolbars to be
shown, there are two ways to make them appear. One is to show all toolbars,
the other is to show a particular toolbar. To show toolbar1, you first have
to create it... If you don't create it first, then all toolbars will be
shown (look at the error statement section that gets called if it can not
verify the existence of the toolbar). On retrospect, I probably should have
the toolbar1 show routine call toolbar1 create, instead of allbars..
(Written when I was about finished with this: Actually the reason I used the
Allbars option, was to ensure that if one toolbar was not already created,
perhaps all of the others have not been created, and therefore to blanketly
create all toolbars to ensure/force them to be "available" for use.
Therefore, no the Toolbar1_Show routine should not be revised.)

Again, though I have yet to actually implement multiple toolbars, but all of
the underlying structure is there. I have been able to get a toolbar to
appear on a sheet, and disappear when I leave the sheet, also when I make the
workbook active with the applicable sheet present, it opens the toolbar, and
when the workbook loses focus, the toolbar disappears.

As for code on other pages. Yes, there is code depending on what you want
to happen that can/should be put on other pages. For example, if you want
something to happen when changing sheets, the Code for that particular
worksheet should include an activate/deactivate section: (I.e.,

Private Sub Worksheet_Activate()
Toolbars.Toolbar1_Show
End Sub

Private Sub Worksheet_DeActivate()
Toolbars.Toolbar1_Hide
End Sub

I'm not clear on this. Where does "Toolbars" come from? VBA doesn't
show "Toolbars" in the Object Browser or in help. And I can't find a
subroutine called Toolbars in your code. More undocumented stuff in VBA
maybe? So I'm unsure what "Toolbars.Toolbar1_Show" does. I did find
this line:
NameBar = Toolbars.TbarName(0)
in your code, another reference to something called Toolbars, which
makes me think Toolbars is an object.
I also looked in your code for "Toolbar1_Hide, but didn't find it.
Finally, I also checked in Walkenbach. He has a number of references to
"CommandBars", and mentions Toolbars as a specific kind of CommandBar.
But I don't find anything there that resembles your use of "Toolbars".


And then something similar with the Workbook ThisWorkbook "sheet". If you
have not seen the list of Microsoft Excel Objects, the chose View-Project
Explorer or something similar.

I'm still with you. You with me? :)

O yes, I'm hard to get rid of, once I decide to take the ride.
I've printed out the code and your last message, so I can look at them
side-by-side, which helps.
I'm trying to figure out where it starts, that is, what part of the
code you showed me is called by the Worksheet.activate event. And then
follow it from there.
I appreciate your help. I know from experience in my old coding days,
that it's usually much harder to figure out someone else's code than to
write it yourself. But since I didn't even know where to start, looking
at yours is probably better in this case.
Thanks for your patience. Maybe after the 3 day weekend, my mind will
be a little sharper. One can always hope... :)


"davegb" wrote:


GB wrote:
In the end... It's actually quite easy from the code I've written. Anytime I
want to "develop" a new toolbar. I go into two areas:

Function Name():
I change the name of the toolbar that I'm trying to use to something unique,
that I hopefully have never developed before/expect to use at the same time
that I'm using this new toolbar.

Ok, I'm going to give this a try. I'll start with a few basic questions
trying to clarify how the macro works. If your patience holds up, I'll
ask more about specific code.
I put your code in a module and rearranged a bit because going from a
module to here to a module, there were lots of lines of red which were
mostly continuations from the previous line.
From your description above, I'm guessing that the code starts at
"Function Name". Is this called by a particular worksheet being
activated? I don't see it being called anywhere else in the code. If
not, then why is it a function instead of just a subroutine?

Looking at Function Name, I'm not clear on what the Select Case
statement is based. Where is "Value" defined? I know it's a zero or a
1, but I can't find where it comes from.
If it's a zero, the toolbar gets named "First Tool Bar", if not, it
doesn't get named.
Finally, since it ends after the Select Case command is executed, how
does the rest of the code get run? Is there more code back at the sheet
itself that calls some other code after Function Name is run?


And then I go into the appropriate Tool_Bar(Number)_Props:
for example Tool_Bar1_Props.
And I revise the toolbar to what I want. If I want a button in a group,
then I use:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlButton)
And then I work with the NewItem (Control Button)

And if I want a Drop down menu to work with, then I:
Set NewItem =
Application.CommandBars(Name(0)).Controls.Add(Type :=msoControlPopup,
Temporary:=True)

And then for every button or item I want to add to that drop down, I use the
newItem and set a ctrl1 variable. The help files on commandbars will tell
you about the different Types, and things like Temporary.

The other thing that I have to change depending on what I expect of the
particular button is the .OnAction command. It has to refer to a procedure
that will do what I want.

In the example provided, if you make a module that is named ModuleName
(Instead of Module1 for example) that has say the public sub routine Move2Del
and Select the drop down menu of the newly created Excel menu that appears
and select the one that says move to the delete folder, it will run whatever
you have programmed in Move2Del.

The FaceId's that I have selected correspond to the actions that I desired
and were fairly readily available.

All in all, the hard part is done for you. And if you get the code into VBA
you will see that like a lot of the Tool_Bar1_Props lines are commented out.
But I have done some different things in there that I do not want to lose so
that if I wish to implement them in the future I have them readily available.

Chr(13) + Char(10) just gives a new line of text
A line ending with an underscore is so that I can have "one" line of code
that I can see completly on the screen and on printouts. It tells VBA that
hey, don't stop processing the next line of code as part of the current line.

If you want to Show ToolBar1 (which in this code, I only implemented one)
then you call Tool_Bar1_Show. The error handling will ensure that it will
appear.

One thing that I forgot to mention was that when the workbook loses focus,
the toolbars should be "put away" or deleted. And then when the workbook
regains focus, the appropriate toolbar(s) should be shown again.

"davegb" wrote:

Wow! Thanks for your reply.
Looks overwhelming. I'm not sure I can figure this one out. I had no
idea it would be this complicated. There are several kinds of syntax
I've never even used before. I think this one's just beyond me at this
point!