Custom Toolbars

[Scott Hicks]

Hello, everyone. I am developing an Excel app for a
company and I have created a custom toolbar. My problem
is that whenever the workbook is copied, the custom
toolbar looks to the original for macros. I would prefer
that the toolbar look in only the current copy for macros,
since this workbook will be used as a template and copied
many times. Does anyone know a way to do this?

Thank you!

[David McRitchie]

Hi Scott,
Did you change the name of the workbook besides the
pathname. I have a macro modified from what
Bernie Dietrick posted for me that might help but there
is almost certainly a better solution. For menus one
such better solution would be "Menu Maker" --
Custom Menus in Excel 97, Tip 53, John Walkenbach
http://www.j-walk.com/ss/excel/tips/tip53.htm

Bernie Deitrick, misc, 2002-04-16, supplied a macro, RepairUserDefinedButtons() to Repair pathnames involving reassigned
personal.xls that get modified when redoing systems or Excel versions. The macro saves work having to change assigned macros on
menus and toolbar buttons when the path to xlstart\personal.xls changes due to system and or Excel version changes.

RepairUserDefinedMenus
http://groups.google.com/groups?selm=uCRotQ05BHA.2004%40tkmsftngp02

I think what I used is slightly different as I want to see
what changes were made. Run this from an empty sheet
it sets down a paper trail. Fixes menus and buttons.
Change pesonal.xls to your workbook name.

Sub RepairUserDefinedMenus()
' Bernie Dietrick, 2002-04-18
Dim CmdBar As CommandBar
Dim myControl As CommandBarControl
Dim Lvl As Long
On Error GoTo ErrorReading:
Dim Fc As Long 'Found in False Count
Lvl = 0 'recursion level
Dim i As Long, j As Long
Fc = 0
Cells.Clear
Cells(1, 1).Select
Cells(1, 1).Value = "pesonal.xls!RepairUserDefinedMenus"
Cells(1, 3) = "Caption"
Cells(1, 4) = "ID"
Cells(1, 5) = "ttiptext"
Cells(1, 6) = "p/o"
Cells(1, 7) = "type"
Cells(1, 8) = "index"
Cells(1, 9) = "name"
Cells(1, 10) = "builtin"
Cells(1, 11) = "action"
Cells(1, 12) = "width"

For Each CmdBar In CommandBars
For i = 1 To CmdBar.Controls.Count
With CmdBar.Controls(i)
If .BuiltIn = False Then
If .Type = msoControlPopup Then
Lvl = Lvl + 1
For Each myControl In .Controls
Fc = Fc + 1
ActiveCell.Offset(Fc * 2 - 1, 0).Value = CmdBar.Position & " in " & CmdBar.Name
If myControl.Type = 10 Then
ActiveCell.Offset(Fc * 2 - 1, 1) = "*menu*"
Else
j = InStr(myControl.OnAction, "!")
If j > 0 Then ActiveCell.Offset(Fc * 2 - 1, 1).Value = Mid(myControl.OnAction, j + 1)
End If
ActiveCell.Offset(Fc * 2 - 1, 2).Value = myControl.Caption
ActiveCell.Offset(Fc * 2 - 1, 3).Value = myControl.ID
ActiveCell.Offset(Fc * 2 - 1, 4).Value = myControl.TooltipText
ActiveCell.Offset(Fc * 2 - 1, 5).Value = "PopUp" ' -- "Control Popup"
ActiveCell.Offset(Fc * 2 - 1, 6).Value = myControl.Type
ActiveCell.Offset(Fc * 2 - 1, 7).Value = myControl.Index
ActiveCell.Offset(Fc * 2 - 1, 8).Value = CmdBar.Name
ActiveCell.Offset(Fc * 2 - 1, 9).Value = CmdBar.BuiltIn
ActiveCell.Offset(Fc * 2 - 1, 10).Value = myControl.OnAction
ActiveCell.Offset(Fc * 2 - 1, 11).Value = CmdBar.Width

If myControl.Type <> 10 Then
j = InStr(1, myControl.OnAction, "pesonal.xls'!")
If j > 0 Then
ActiveCell.Offset(Fc * 2 - 1, 10).Value = myControl.OnAction
myControl.OnAction = _
"'pesonal.xls'!" & Mid(myControl.OnAction, j + 13)
ActiveCell.Offset(Fc * 2, 10).Value = _
"'pesonal.xls'!" & Mid(myControl.OnAction, j + 13)
End If ' J> 0
Lvl = Lvl - 1
End If '.type
Next myControl
Else
Fc = Fc + 1
ActiveCell.Offset(Fc * 2 - 1, 0).Value = i & " in " & CmdBar.Name
On Error Resume Next
j = InStr(.OnAction, "!")
If j > 0 Then ActiveCell.Offset(Fc * 2 - 1, 1).Value = Mid(.OnAction, j + 1)
ActiveCell.Offset(Fc * 2 - 1, 2).Value = .Caption
ActiveCell.Offset(Fc * 2 - 1, 3).Value = .ID
ActiveCell.Offset(Fc * 2 - 1, 4).Value = .TooltipText
ActiveCell.Offset(Fc * 2 - 1, 5).Value = "Other"
ActiveCell.Offset(Fc * 2 - 1, 6).Value = .Type
ActiveCell.Offset(Fc * 2 - 1, 7).Value = .Index
ActiveCell.Offset(Fc * 2 - 1, 8).Value = CmdBar.Name
ActiveCell.Offset(Fc * 2 - 1, 9).Value = CmdBar.BuiltIn
ActiveCell.Offset(Fc * 2 - 1, 10).Value = .OnAction
ActiveCell.Offset(Fc * 2 - 1, 11).Value = CmdBar.Width
j = InStr(1, CmdBar.Controls(i).OnAction, "pesonal.xls'!")
If j > 0 Then
.OnAction = "'pesonal.xls'!" & Mid(.OnAction, j + 13)
ActiveCell.Offset(Fc * 2, 10).Value = _
"'pesonal.xls'!" & Mid(.OnAction, j + 13)
End If ' J> 0
End If ' .type / ELS
End If ' .builtin
End With 'cmdbar
ErrorReading:
Next i
Next CmdBar
End Sub

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Scott Hicks" <dscot...@hotmail.com> wrote in message news:1ca201c1f775$1fbe3d00$3aef2ecf@TKMSFTNGXA09...

[Jon Peltier]

In addition to David's response, I can describe my own practice. You can,
probably should, create a workbook-specific command bar when the workbook
opens, using the temporary:=true parameter, and kill it when the book closes.
This way, if the command bar exists, it is up to date. Also, use the
workbook's name as a prefix in the OnAction property:

With myCustomButton
.OnAction = ThisWorkbook.Name & "!MyFavoriteMacro"
End With

This way it always points to the right workbook, and if some other workbook
has a procedure with the same name, there will be no confusion about which to
run.

- Jon
_______

In article <1ca201c1f775$1fbe3d00$3aef2ecf@TKMSFTNGXA09>,
dscot...@hotmail.com says...