I've created a custom toolbar that is Attached to a particular workbook
file. Every time I open that file, the custom toolbar appears as it is
supposed to. However, none of the toolbar buttons that I put on the toolbar
appear with it, even though I saved the workbook after I put the buttons one
during the last session.

I do have some code that deletes the custom toolbar from the workbook when
the workbook closes so the toolbar is only open when that file is open, but
I wouldn't expect this to have any effect on whether the toolbar buttons
remain on the toolbar. On the outside chance this is causing my problem,
here's the code I'm using for that purpose:

***************
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("TimesheetToCalStars").Del ete
End Sub

Sub Auto_Close()
On Error Resume Next
Application.CommandBars("TimesheetToCalStars").Del ete
End Sub
***************

What can I do to get these toolbar buttons to remain on the toolbar the next
time I open the workbook?

Thanks in advance.

If you want to do it this way, I think youll have to
reconstruct the toolbar and make it invisible on close
instead of deleting it (".Visible = False"). However,
you'll probably also have to change the toolbar name as
well. I confess to not fully understanding the issue,
but, as I have it, once a toolbar with a given name is on
record in the .xlb file it will always reproduce the first
toolbar on record with this name EVEN AFTER YOU HAVE
DELETED IT !!!

Assuming you find this to be the case and if you don't
want to change the name then you can delete the .xlb file
so long as there are no other needed custom toolbars
listed on the computer. Excel will recreate it if it
cannot find it.

Preferred alternative:
I believe most professionals (I'm not one) prefer to
create their toolars on the fly to avoid complications.
A "quick and dirty" reproduction of what I do follows.
Note that the toolbar's Temporary parameter is set to True
so it will automatically delete upon closing Excel (no
code required).

'Paste to the ThisWorkbook Module:
Option Base 1
Private Sub Workbook_Open()
Call MakeToolBar
End Sub

Private Sub MakeToolBar()
Dim NewTB As CommandBar, NewBut As CommandBarButton
Dim Ar1 As Variant, Ar2 As Variant
Dim Ar3 As Variant, Ar4 As Variant
Dim i As Integer

Ar1 = Array
("Calculate", "ListNames", "DeleteBlanks", "ListOT")
Ar2 = Array(283, 222, 1786, 521)
Ar3 = Array("Calculate hours", "List names", "Delete blank
entries", "List OT hours")
Ar4 = Array("Calculates employee weekly hours", "Lists
employee names", "Deletes blank entries", "Lists total OT
hours for group")
Application.ScreenUpdating = False
Set NewTB = Application.CommandBars.Add(Name:="TestTB",
Temporary:=True)
NewTB.Visible = True
For i = 1 To 4
Set NewBut = NewTB.Controls.Add(Type:=msoControlButton)
With NewBut
.OnAction = Ar1(i)
.FaceId = Ar2(i)
.Caption = Ar3(i)
.TooltipText = Ar4(i)
.Style = msoButtonIconAndCaption
End With
Next
Application.ScreenUpdating = True
End Sub

Hope I was of some help. A reminder that I'm only a
student.

Regards,
Greg Wilson

-----Original Message-----
I've created a custom toolbar that is Attached to a
particular workbook
file. Every time I open that file, the custom toolbar
appears as it is
supposed to. However, none of the toolbar buttons that I
put on the toolbar
appear with it, even though I saved the workbook after I
put the buttons one
during the last session.

I do have some code that deletes the custom toolbar from
the workbook when
the workbook closes so the toolbar is only open when that
file is open, but
I wouldn't expect this to have any effect on whether the
toolbar buttons
remain on the toolbar. On the outside chance this is
causing my problem,
here's the code I'm using for that purpose:

***************
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("TimesheetToCalStars").Del ete
End Sub

Sub Auto_Close()
On Error Resume Next
Application.CommandBars("TimesheetToCalStars").Del ete
End Sub
***************

What can I do to get these toolbar buttons to remain on
the toolbar the next
time I open the workbook?

Thanks in advance.



.

Greg - thank you for the explanation and the code. In addition to
addressing the question I asked, it was a good illustration of using arrays
and VBA code to create a toolbar. This is going to be helpful to me in
several areas.

Having said that, I'm surprised that if you have an Attached toolbar, you
still need to recreate it in order to use it in the workbook. If that't the
case, what's the point of having an attached toolbar in the first place? Is
there anyone out there who can tell us if there's a way to get an attached
toolbar to appear with all it's toolbar buttons on in when you open the
workbook in which the toolbar is attached?

Thanks much.

My bet is you missed a step when you did it manually.

Jan Karel Pieterse has posted some nice instructions for attached toolbars:
http://google.com/groups?threadm=083...0a% 40phx.gbl

And I don't think you need both Auto_close and workbook_beforeclose in your
project (since they're doing the same thing).



Paul James wrote:

Greg - thank you for the explanation and the code. In addition to
addressing the question I asked, it was a good illustration of using arrays
and VBA code to create a toolbar. This is going to be helpful to me in
several areas.

Having said that, I'm surprised that if you have an Attached toolbar, you
still need to recreate it in order to use it in the workbook. If that't the
case, what's the point of having an attached toolbar in the first place? Is
there anyone out there who can tell us if there's a way to get an attached
toolbar to appear with all it's toolbar buttons on in when you open the
workbook in which the toolbar is attached?

Thanks much.

--

Dave Peterson

Thanks for the information, Dave. I'll check out Jan's instructions.

You're probably right that I missed a step. Greg Wilson had some specific
suggestions about what I might be missing in his last message, so I'm going
to check those out as well.

I like Greg's suggestion better than attaching the toolbar, too. But
sometimes it's good to know why something didn't work.

"Paul James" wrote in message news:<5YIXa.69676$YN5.54371@sccrnsc01...
Thanks for the information, Dave. I'll check out Jan's instructions.

You're probably right that I missed a step. Greg Wilson had some specific
suggestions about what I might be missing in his last message, so I'm going
to check those out as well.

Dave - Jan said that "if you want a certain menu-item or toolbar button to
be unavailable whilst certain workbooks are visible (or only available
whilst one workbook is there), you need to use VBA code to show and hide
them." I think he's mistaken about that. For my explanation on this,
please see my latest reply to Greg Wilson elsewhere in this thread.

Again, you were exactly right when you said I missed a step when I did it
manually. A "attached" the toolbar to the workbook before I put the command
buttons on the toolbar. The key is to build the toolbar by putting
everything on it before attaching it to the workbook.

Thanks.

Jan Karel was writing about having multiple workbooks open (including the one
with the attached toolbar) and hiding that toolbar when you're in one of the
other workbooks.

Jan Karel was suggesting workbook_activate to show them and workbook_deactivate
to hide them from the other workbooks.

Paul James wrote:

Dave - Jan said that "if you want a certain menu-item or toolbar button to
be unavailable whilst certain workbooks are visible (or only available
whilst one workbook is there), you need to use VBA code to show and hide
them." I think he's mistaken about that. For my explanation on this,
please see my latest reply to Greg Wilson elsewhere in this thread.

Again, you were exactly right when you said I missed a step when I did it
manually. A "attached" the toolbar to the workbook before I put the command
buttons on the toolbar. The key is to build the toolbar by putting
everything on it before attaching it to the workbook.

Thanks.

--

Dave Peterson

Hi Paul,

I actually meant for you to abandon altogether the method
of creating a toolbar and attaching it to the workbook.
Instead, just paste my code to the ThisWorkbook module.
To run my demo code, you'll have to create four dummy
macros in a standard module with the referenced macro
names. These will be called when you press the buttons.
Delete your toolbar and remove the reference from the
Attached list.

My understanding of attaching toolbars to a workbook is
that the original version of the toolbar when it was
attached is the one that is recalled upon opening the
workbook. It does not automatically update to a revised
version. You must manually unattach the toolbar, revise
it, then reattach it. My theory as to your situation is
that a version of your toolbar without buttons was
originally attached if that is possible. To resolve this,
try unattahing the toolbar, deleting it, then recreating
and reattaching.

Note that an experiment I conducted proved that even if
you delete an attached toolbar (as does your code), the
original version (when it was attached) will reappear when
the workbook is opened.

I also suggest that you NOT delete the toolbar upon close
but to make it invisible instead and also make it visible
again upon reopen using the Workbook_BeforeClose and
Workbook_Open events respectively. If my suggestions are
not successful, note that if you delete the .xlb file then
the old version will be destroyed. Of course, it will
destroy ALL toolbar customizations as well. Excel
automatically recreates the .xlb file if it has been
deleted.

The main reason I prefer the create-on-the-fly method is
because if someone saves your workbook under a different
name (using SaveAs) then all the macro references will
link to the new workbook and will no longer work for the
original version. If you have 20 or so buttons then this
is a real bitch !!! And from my experience, this will
happen a whole lot !!!

Try this experimentally but close the new workbook after
creating it. If you have created your own button images
using the button image editor then there is a way to deal
with this.

Good luck,
Greg





-----Original Message-----
Greg - thank you for the explanation and the code. In
addition to
addressing the question I asked, it was a good
illustration of using arrays
and VBA code to create a toolbar. This is going to be
helpful to me in
several areas.

Having said that, I'm surprised that if you have an
Attached toolbar, you
still need to recreate it in order to use it in the
workbook. If that't the
case, what's the point of having an attached toolbar in
the first place? Is
there anyone out there who can tell us if there's a way
to get an attached
toolbar to appear with all it's toolbar buttons on in
when you open the
workbook in which the toolbar is attached?

Thanks much.


.