Custom Toolbars Again!!!

Sorry to be a pain about this. The custom toolbar I created is displaying
when the workbook opens and hidden when the workbook closes, which is fine.
I then wanted to copy the workbook to another location with another name, and
attached the toolbar to the new workbook. However, the damn thing keeps
looking back to the original workbook for the macros. Is the only way to
create a new toolbar name for each workbook?
--
Sharon

Hi Sharon.
When you created the custom toolbar, the toolbar was saved in "Excel.xlb"
NOT the workbook that was open when you created it. So, when you created the
buttons on the toolbar, you pointed them at the macros in the workbook that
was open at the time. Now, when you open the toolbar (regardless of which
workbooks are open), those macros look for the macros you've assigned to
them...which are in the original workbook. Hope that explains what happened.
To get around this, you might want to put code in the workbook that will
create a toolbar on the fly each time you open the workbook [and destroy it
each time you close the workbook]. That way the code for the toolbar as well
as the macros in the workbook are ALL copied to a new workbook.
John Walkenbach has a couple of excellent books out that explains this
technique.
Check out 'Excel 2003 Power Programming with VBA' at
http://j-walk.com/ss/books/index.htm

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Sharon" wrote:

> Sorry to be a pain about this. The custom toolbar I created is displaying
> when the workbook opens and hidden when the workbook closes, which is fine.
> I then wanted to copy the workbook to another location with another name, and
> attached the toolbar to the new workbook. However, the damn thing keeps
> looking back to the original workbook for the macros. Is the only way to
> create a new toolbar name for each workbook?
> --
> Sharon

Sharon,

Here's a template for doing what Gary suggests. (j-walk's site will be more
complete).

You would paste this code into the ThisWorkbook module:

Private Sub Workbook_Activate()
Call make_menu
End Sub

Private Sub Workbook_Deactivate()
Call delete_menu
End Sub

Sub make_menu()
Dim cbar As CommandBar
Dim cbarcontrol As CommandBarControl
'delete the previous version if it exists
Call delete_menu
Set cbar = Application.CommandBars.Add(Name:="tester", temporary:=True)
cbar.Visible = True
Set cbarcontrol = cbar.Controls.Add(Type:=msoButtonIcon)
With cbarcontrol
.FaceId = 2
.OnAction = "tester"
End With

End Sub

Sub delete_menu()
On Error Resume Next
Application.CommandBars("tester").Delete
On Error GoTo 0
End Sub

Then create a macro "tester" in a regular module:

Sub tester
msgbox "tester"
End Sub

hth,

Doug

"Sharon" <[email protected]> wrote in message
news:[email protected]...
> Sorry to be a pain about this. The custom toolbar I created is displaying
> when the workbook opens and hidden when the workbook closes, which is
fine.
> I then wanted to copy the workbook to another location with another name,
and
> attached the toolbar to the new workbook. However, the damn thing keeps
> looking back to the original workbook for the macros. Is the only way to
> create a new toolbar name for each workbook?
> --
> Sharon

Gary and Doug - thanks so much - helped a lot and will make the toolbar on
the fly.
--
Sharon


"Doug Glancy" wrote:

> Sharon,
>
> Here's a template for doing what Gary suggests. (j-walk's site will be more
> complete).
>
> You would paste this code into the ThisWorkbook module:
>
> Private Sub Workbook_Activate()
> Call make_menu
> End Sub
>
> Private Sub Workbook_Deactivate()
> Call delete_menu
> End Sub
>
> Sub make_menu()
> Dim cbar As CommandBar
> Dim cbarcontrol As CommandBarControl
> 'delete the previous version if it exists
> Call delete_menu
> Set cbar = Application.CommandBars.Add(Name:="tester", temporary:=True)
> cbar.Visible = True
> Set cbarcontrol = cbar.Controls.Add(Type:=msoButtonIcon)
> With cbarcontrol
> .FaceId = 2
> .OnAction = "tester"
> End With
>
> End Sub
>
> Sub delete_menu()
> On Error Resume Next
> Application.CommandBars("tester").Delete
> On Error GoTo 0
> End Sub
>
> Then create a macro "tester" in a regular module:
>
> Sub tester
> msgbox "tester"
> End Sub
>
> hth,
>
> Doug
>
> "Sharon" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry to be a pain about this. The custom toolbar I created is displaying
> > when the workbook opens and hidden when the workbook closes, which is
> fine.
> > I then wanted to copy the workbook to another location with another name,
> and
> > attached the toolbar to the new workbook. However, the damn thing keeps
> > looking back to the original workbook for the macros. Is the only way to
> > create a new toolbar name for each workbook?
> > --
> > Sharon
>
>
>