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!