See below for new code that I have provided you. Implemented a total of
three menu bars (Commandbars) All of them kinda' have the same properties,
so hard to tell that they are different, but you can change that yourself. :)

I implemented the commonbutton subroutine, to add the three buttons that you
wanted that would be common, and verified that I could get the toolbars to
appear on the appropriate sheet, and not appear when using a different
workbook.

Good luck, think you are in the final stretches.

As for the error handling, well, I implemented each one as I figured out
where it errored. Never got additional errors outside of normal usage of the
toolbars. Though that may not be true in this instance, since I never
implemented more than one toolbar before. *smirk* Tried to design for it, but
never did it.

"davegb" wrote:


GB wrote:
Content was this... Review the help files on the commandbar protection, and
the commandbar position... Those two together should allow you to force the
toolbar to a location and prevent the user from changing it. You can only
choose one position, but you can force multiple protections by summing the
protections (I.e., msoBarNoCustomize + msoBarNoChangeDock)

The other thing was a way to write the code one time to add the three common
buttons into each toolbar. Basically write a subroutine that receives the
TBRnumber. Inside that subroutine, add the three buttons to the
tbrName(TBRNumber). This will put the three common features first. The
subroutine is called from the appropriate Tool_Bar(x)_Props sheet just after
the toolbar has been created. All other buttons are added after that...
Sorry for the brevity, I have a meeting in 15 mins.

I appreciate your patience here.
I'm still piecing this together. So I'm trying to figure out the
sequence of the subroutines you're calling. (Not worrying so much about
what each one does, though I am looking as I go.) Mostly trying to
figure out the order in which all these happen.

You start with Public Sub All_Bars, which calls Tool_Bar1_Create which
calls Tool_Bar1_Props.
Tool_Bar1_Props creates a toolbar, using the remamed function TbrName.
Menubar is the toolbar, but it's also a property of CommandBars?

The section of code where the "Set NewItem=" appears creates the
buttons.
The remarked out sections create additional tools.

It exits Tool_Bar1_Props, and returns to Tool_Bar1_Create, then back to
Sub All_Bars.
All_Bars_Delete is called if there's an error with hiding the toolbar.
All_Bars_Hide must be called from the spreadsheet when the user exits?

I'm not clear as to why "All_Bars" is called under ShowErr, seems like
this could easily create an endless loop. Not so?

Is this correct? Is this the order in which the program runs?



"GB" wrote:

Dog gone it..... I just wrote a full length reply, sorta' like the answer of
all answers. I didn't copy it before hitting the post, and now I think it is
lost in the ether world.

I'll have to get back to you... :\

"davegb" wrote:


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

I follow, in theory, most of what you've said. I'm still having trouble
knowing how to start writing/modifying code. Let me explain my scenario
and see if you can help me get started.
I have 9 worksheets the end user can access in the workbook (other
sheets hidden but not relevant to this discussion). I've written macros
and put large buttons on the spreadsheet to apply various filters to
each of the 9 sheets. 3 of the button/macros apply to all 9 sheets. One
sheet has only these 3. The other sheets have various combinations of
those 3 plus other buttons/macros. They have up to 7 buttons/macros. We
need to display more data on these sheets, so the space where the
buttons are is needed for that. So now I want to have a toolbar show
up, preferably at the bottom of the screen, when the end-user selects
that sheet. It doesn't have to go away when they are done. And I don't
want to hide the standard and formatting toolbars when my toolbar is
displayed. Some of my end-users can barely navigate these sheets, and I
want to avoid any confusion. That's why I want the special toolbars at
the bottom of the screen, do they won't confuse them with the standard
ones. I don't want the standard ones hidden because the might use them
to copy data from the sheet and because not having the standard
toolbars might confuse them.
Overall, my situation is much simpler than yours.
So what I want is to click on Sheet5 and have toolbar x show up with
buttons a, b, c, f, h. Then click on Sheet7 and have toolbar y with
buttons a, b, c, g, i.
So my question is, "what is the best strategy for doing this?" Do I
just create and save the 9 toolbars, then call them as needed when the
sheet is selected? Or do I create them on the fly each time the
appropriate sheet is selected?
And they don't need to have button faces, except for the 3 standard
ones. Their functions are too complex to be represented that way, so I
just want a short name to show up on the button. I've already created a
"prototype" toolbar this way.
As I'm describing this to you, I'm questioning whether any VBA is
required at all, since the toolbars don't have to be hidden and
re-activated. Is there a way to create the 9 toolbars, using some of
the same buttons, and then assign those toolbars to the appropriate
sheet? Of course, I don't want more than just that custom toolbar and
the standard toolbars (standard and formatting) on each sheet. And when
another workbook is opened, none of these toolbars should be present.
Is that doable without any code?






"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: