saving toolbar buttons on custom toolbar

[Paul James]

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").Delete
End Sub

Sub Auto_Close()
On Error Resume Next
Application.CommandBars("TimesheetToCalStars").Delete
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 Wilson]

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

 

[Paul James]

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]

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/[email protected]

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

 

[Greg Wilson]

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

 

[Paul James]

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.

 

[Greg Wilson]

Hi Paul,

What happens when you assign a macro to a toolbar button
is that not only is the name of the macro referenced for
the particular button but the workbook of origin as well.
To prove this, add a button to the Worksheet Menu Bar at
the end (far right) and assign it a macro. Then run the
following code:

Sub TestMacroAssignment()
Dim X As Integer
X = Application.CommandBars(1).Controls.Count
MsgBox Application.CommandBars(1).Controls(X).OnAction
End Sub

You will get a message referencing the workbook followed
by the name of the macro that was assigned. If your
workbook is named "Timesheets.xls" then you'll get a
message something like:
'C:\Windows\Desktop\Timesheet.xls'!MyMacro

Remember, toolbars are global to Excel as opposed to a
particular workbook. When you save a workbook under a
different name you DO NOT also create a duplicate series
of toolbars including custom toolbars.

What happens when someone saves the workbook under a
different name (SaveAs) is that Excel reassigns the
workbook references for custom toolbar button macros to
the new workbook. In other words, if you were to now run
the above code with the new workbook open you would now
return a message something like this:
"C:\Windows\MyDocuments\SalesDivTimesheets.xls'!MyMacro

After some evil person has done this and closed the new
workbook, if you now come along and open the original
workbook and click on the same button that was added to
the Worksheet Menu Bar, it will return an error message
something like: Cannot find macro "MyMacro" or such.
This is because it is looking for the code in the new
workbook which is closed.

In my experience, this is likely to occur frequently by
people who treat your workbook as a template to crunch the
numbers and then save their work under a different name.
However, if you follow my suggestion, whenever the
original or any duplicate workbooks are opened, the
toolbars and buttons are created anew and the macros are
also assigned anew to each button. Upon closing the
workbooks, they are also deleted automatically. Problem
solved. I suggest you check it out experimentally.

As I said previously, if you have edited the button images
and want to use the customized images, there is also a way
to accomodate this.

Regards,
Greg

 

[Dave Peterson]

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]

Greg - again, thank you for all the information. I ran the
TestMacroAssignment() sub and you're right, it did display the workbook name
in the macro assignment designation. But then that code displays the macro
assignment for a menu bar item. Do you know what the code would be to
display the same thing for the toolbar buttons?

The reason I ask is that I opened another workbook that I created some time
ago that has a custom toolbar attached to the workbook. This workbook
doesn't recreate the toolbar when it opens, the toolbar is simply attached
to the workbook.

I renamed that workbook to see if the toolbar would still open when the
workbook opens (it does), and the toolbar buttons also function properly,
even though they're in a workbook with a different name. So it would seem
that the macro assignments on those toolbar buttons are not dependent on the
name of the workbook.

When I close the workbook, I delete the toolbars using
"Application.CommandBars("toolbar name").Delete" in the Auto_Close sub. And
after that workbook is closed, the toolbar no longer appears in the list of
toolbars in the application.

These observations seem to suggest that the toolbar goes with the workbook.
I think one of the key steps might be that you need to place all of the
buttons and macro assignments in the toolbar before you Attach it to the
workbook. But it seems to work well, even after you rename the workbook,
without having to rebuild the toolbar every time you open the workbook.

Try it yourself as an experiment, following these steps:

- create a Sub procedure
- create a new toolbar, place a new button on the toolbar and assign that
sub procedure to the button
- before you close the Customize dialog, go back to the Toolbars tab and
Attach the new toolbar to the workbook.
- add this sub to a module in the workbook:
Sub Auto_Close()
On Error Resume Next
Application.CommandBars("toolbar name").Delete
End Sub
- try out the toolbar button to confirm that it's calling the sub
- save the workbook, close then reopen, then save it with another name
- open the renamed workbook and you'll see that the custom toolbar is still
there

You'll also notice that after you close the workbook, the custom toolbar no
longer appears in the list of toolbars in the application. It's only there
when the workbook that contains the "attached" toolbar is open.

In view of the foregoing, it seems to me that we don't need to recreate
custom toolbars in VBA code in order to use them with a workbook. Unless
there's something I'm missing.

Paul

 

[Paul James]

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.

 

[Paul James]

I need to correct something I said in my previous post here. The delete
method of the toolbars object needs to be located in the BeforeClose event
of the workbook, not the Auto_Close. This is the way to programmatically
get rid of the toolbar when the workbook to which it's attached isn't open.

Here's the code:

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

Sorry for any confusion.

Paul

 

[Dave Peterson]

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.

 

[Greg Wilson]

Hi Paul,

First, I appologize for not checking out my theory before
posting as I was wrong. It was late and I was tired. The
same is true tonight unfortunately. I keyed into your
post because this issue had been the bain of my existence
for some time until I arrived at my solution (which does
work).

From my observations, when you save a workbook under a
different name then the macros get reasigned to the new
workbook. If you now close the new workbook and go back
and open the original, when you push a button on the
toolbar, it will automactically open the new workbook
(contrary to what I said) in order to access the macro
code. This assumes that it can find the new workbook.

To prove that the new workbook gets opened automatically
when you push a button, check the list of open workbooks
using the Window menu item in the Worksheet Menu Bar
before and after pushing a button.

What was happening that drove me nuts was that frequently,
someone would use my workbook as a template to crunch the
numbers and then save the results (the workbook) either
to some place on our intranet, or alternatively, to a
floppy, and then walk off with the floppy. The macros
would reassign to the new workbook; i.e., to someplace on
the intranet or to a floppy that rode off into the sunset.

Therefore, when you subsequently attempted to run the
macros from the origninal workbook, Excel would look for
the code in the new workbook. But, in my case, it
couldn't find the new workbook. I'm not sure why, but
Excel couldn't access the new workbook through the
intranet and obviously couldn't do so for an absent
floppy. Therefore, I would have to manually reassign the
macros to the toolbar buttons.

What I've learned from you is that after attaching a
toolbar to a workbook, it is crucial to have code that
DELETES it on close as opposed to making it invisible.
(This is what you were doing and the exact opposite to
what I advised so I again appologize). The importance of
this is that:
i) If someone makes a copy, then it too has a record
of "Toolbar X" attached and also has code that will
delete "Toolbar X" on close. So "Toolbar X" always gets
deleted.
ii) When a workbook with an attached toolbar is opened and
it can't find the attached toolbar because it has been
deleted then it will RECREATE it. The recreated version
is the one that existed when it was first attached to that
workbook. And the toolbar button macro assignments will
of course point to macros contained in the workbook.
iii) On the other hand, if "Toolbar X" does not get
deleted then it will not get recreated and so a toolbar
with macro assignments pointing to another workbook will
persist.

This is not a brilliant insight but a major transformation
of my thinking. I never read anywhere that you should
delete the toolbar (seems like a dumb idea after all that
work). I do recall examples of making it invisible.

I did caution you that I'm only a student. I've done a
few things for my employer unpaid and study VBA for
personal development. I hope I'm not still confused and
just think I understand. What puzzles me is that the
create-on-the-fly method that I use is commonly done by
many professionals 'in order to avoid complications'.

Ironically, THANKS FOR THE HELP!!! I think I'll stop
responding to these posts until I get more experience.

So did you ever get your toolbar to work ???

Best regards,
Greg