Dave Peterson wrote in
:

Thanks! Lots of stuff to read over. Looks like it will be useful. I knew I
must have missed the solution(s).

Ps. David McRitchie has lots of notes he
http://www.mvps.org/dmcritchie/excel/toolbars.htm
Look for:
Repair Renamed Pathnames on Menus and Toolbar Buttons
and you'll see a link to a newsgroup post by Bernie Deitrick

http://groups.google.com/group/micro...msg/d692c7e54d
fe909b?as_umsgid=eNXt5XX5BHA.1020@tkmsftngp07

Or

http://snipurl.com/121mv

bz wrote:

Google shows me many who have asked this question, but no solutions.
The problem seems to have existed since the early days of VB macros
being incorporated into excel.

The problem: intermittently when an excel document with VB macros
attached to a menu bar and/or a task bar is save-as renamed, the links
to the macros point to the OLD excel file's name[with the full path
being given!]. This makes the wrong macros execute or give an error
message when attempting to execute the macros.
.....


--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

Dave Peterson wrote in
:

Sorry, looked over the links and they seem aimed at fixing links to macros
stored in personal.xls.

My problem is when I rename my workbook via SaveAs, the manually attached
macros (attached to BOTH a menu bar and a toolbar) that are part of the
workbook in question, INTERMITTENTLY get OnAction values that include the
entire path and filename rather than just the macro's name.

If the file is renamed or moved, the macros will then fail to work, or I end
up executing the WRONG macros (those in the old location). This is especially
problematic when I send someone the workbook so that they can run it on their
system.

I want to REMOVE everything except the macro's name so that excel will look
in the CURRENT workbook for the macro.

It appear that I will have to write a macro to do something like the
following:

for i = 1 to CmdBar.Controls.count
If CmdBar.Controls(i).BuiltIn = True and _
CmdBar.Controls(i).Caption = "MY Command Bar" Then
For j = 1 To CmdBar.Controls(i).Controls.Count
If InStr(1, CmdBar.Controls(i).Controls(j).OnAction, ":\") 0 Then
Dim ActionString as String
ActionString = CmdBar.Controls(i).Controls(j).OnAction
CmdBar.Controls(i).Controls(j).OnAction = _
Right(ActionString, Len(ActionString) - InStr(1, ActionString, _
"!")+1)
End if
Next j
End if
Next i

Untested as of yet. I am NOT sure if this will fix both Menu Bars and Tool
Bars. I am not even sure it will work.

Do you see any obvious flaws or improvements that could be made easily?

Thanks.

Ps. David McRitchie has lots of notes he
http://www.mvps.org/dmcritchie/excel/toolbars.htm
Look for:
Repair Renamed Pathnames on Menus and Toolbar Buttons
and you'll see a link to a newsgroup post by Bernie Deitrick

http://groups.google.com/group/micro.../msg/d692c7e54
d fe909b?as_umsgid=eNXt5XX5BHA.1020@tkmsftngp07

Or

http://snipurl.com/121mv

bz wrote:

Google shows me many who have asked this question, but no solutions.
The problem seems to have existed since the early days of VB macros
being incorporated into excel.

The problem: intermittently when an excel document with VB macros
attached to a menu bar and/or a task bar is save-as renamed, the links
to the macros point to the OLD excel file's name[with the full path
being given!]. This makes the wrong macros execute or give an error
message when attempting to execute the macros.
....





--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

[remove c h 1 0 0 - 5 to avoid spamtrap]

Personally, I try not to have to worry about reassigning the buttons to the
macros in the correct workbook. I build the toolbar/menu items when the
workbook opens.

Life got lots easier when I started doing that.

bz wrote:

Dave Peterson wrote in
:

Sorry, looked over the links and they seem aimed at fixing links to macros
stored in personal.xls.

My problem is when I rename my workbook via SaveAs, the manually attached
macros (attached to BOTH a menu bar and a toolbar) that are part of the
workbook in question, INTERMITTENTLY get OnAction values that include the
entire path and filename rather than just the macro's name.

If the file is renamed or moved, the macros will then fail to work, or I end
up executing the WRONG macros (those in the old location). This is especially
problematic when I send someone the workbook so that they can run it on their
system.

I want to REMOVE everything except the macro's name so that excel will look
in the CURRENT workbook for the macro.

It appear that I will have to write a macro to do something like the
following:

for i = 1 to CmdBar.Controls.count
If CmdBar.Controls(i).BuiltIn = True and _
CmdBar.Controls(i).Caption = "MY Command Bar" Then
For j = 1 To CmdBar.Controls(i).Controls.Count
If InStr(1, CmdBar.Controls(i).Controls(j).OnAction, ":\") 0 Then
Dim ActionString as String
ActionString = CmdBar.Controls(i).Controls(j).OnAction
CmdBar.Controls(i).Controls(j).OnAction = _
Right(ActionString, Len(ActionString) - InStr(1, ActionString, _
"!")+1)
End if
Next j
End if
Next i

Untested as of yet. I am NOT sure if this will fix both Menu Bars and Tool
Bars. I am not even sure it will work.

Do you see any obvious flaws or improvements that could be made easily?

Thanks.

Ps. David McRitchie has lots of notes he
http://www.mvps.org/dmcritchie/excel/toolbars.htm
Look for:
Repair Renamed Pathnames on Menus and Toolbar Buttons
and you'll see a link to a newsgroup post by Bernie Deitrick

http://groups.google.com/group/micro.../msg/d692c7e54
d fe909b?as_umsgid=eNXt5XX5BHA.1020@tkmsftngp07

Or

http://snipurl.com/121mv

bz wrote:

Google shows me many who have asked this question, but no solutions.
The problem seems to have existed since the early days of VB macros
being incorporated into excel.

The problem: intermittently when an excel document with VB macros
attached to a menu bar and/or a task bar is save-as renamed, the links
to the macros point to the OLD excel file's name[with the full path
being given!]. This makes the wrong macros execute or give an error
message when attempting to execute the macros.
....



--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

[remove c h 1 0 0 - 5 to avoid spamtrap]

--

Dave Peterson