task bar attached macro name probles

[bz]

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.

If the file names [and paths] were NOT stored, things would work fine. If
the full path were not specified AND the correct file name were stored,
everything would still be ok.

I realize that there may be times when one would WANT to execute macros
that were NOT part of the current spread sheet. I just don't know how to
tell excel that this is NOT one of those times and make sure it ALWAYS
knows that. Perhaps I have missed seeing a 'check box' somewhere.

It would seem possible to write a macro that would search through and
change every 'path/filename!macroname' to 'macroname'
(or should it be '!macroname'?),
and make this macro auto-execute when ever the spreadsheet is saved, but
the fact that no one seems to have done it would seem to indicate that
there are problems in creating such a solution. Have I missed seeing an
already published solution?

[Why in the world hasn't MS fixed this problem by now? is another question]

--
bz

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

(e-mail address removed) [remove c h 1 0 0 - 5 to avoid spamtrap]

 

[Dave Peterson]

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

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.

If the file names [and paths] were NOT stored, things would work fine. If
the full path were not specified AND the correct file name were stored,
everything would still be ok.

I realize that there may be times when one would WANT to execute macros
that were NOT part of the current spread sheet. I just don't know how to
tell excel that this is NOT one of those times and make sure it ALWAYS
knows that. Perhaps I have missed seeing a 'check box' somewhere.

It would seem possible to write a macro that would search through and
change every 'path/filename!macroname' to 'macroname'
(or should it be '!macroname'?),
and make this macro auto-execute when ever the spreadsheet is saved, but
the fact that no one seems to have done it would seem to indicate that
there are problems in creating such a solution. Have I missed seeing an
already published solution?

[Why in the world hasn't MS fixed this problem by now? is another question]

--
bz

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

(e-mail address removed) [remove c h 1 0 0 - 5 to avoid spamtrap]

 

[JLGWhiz]

I would imagine that there are too many variables for such a program to be
included in the Excel software for copying and moving filles from folder to
folder, between mainframes and servers, since Microsoft has no idea how
individuals compose their macros.

 

[Dave Peterson]

Ps. David McRitchie has lots of notes here:
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/micr...fe909b?as_umsgid=eNXt5XX5BHA.1020@tkmsftngp07

Or

http://snipurl.com/121mv

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.

If the file names [and paths] were NOT stored, things would work fine. If
the full path were not specified AND the correct file name were stored,
everything would still be ok.

I realize that there may be times when one would WANT to execute macros
that were NOT part of the current spread sheet. I just don't know how to
tell excel that this is NOT one of those times and make sure it ALWAYS
knows that. Perhaps I have missed seeing a 'check box' somewhere.

It would seem possible to write a macro that would search through and
change every 'path/filename!macroname' to 'macroname'
(or should it be '!macroname'?),
and make this macro auto-execute when ever the spreadsheet is saved, but
the fact that no one seems to have done it would seem to indicate that
there are problems in creating such a solution. Have I missed seeing an
already published solution?

[Why in the world hasn't MS fixed this problem by now? is another question]

--
bz

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

(e-mail address removed) [remove c h 1 0 0 - 5 to avoid spamtrap]

 

[bz]

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 here:
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/microsoft.public.excel.misc/msg/d692c7e54d
fe909b?as_umsgid=eNXt5XX5BHA.1020@tkmsftngp07

Or

http://snipurl.com/121mv

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.

(e-mail address removed)

 

[bz]

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 here:
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/microsoft.public.excel.misc/msg/d692c7e54
d fe909b?as_umsgid=eNXt5XX5BHA.1020@tkmsftngp07

Or

http://snipurl.com/121mv

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.

(e-mail address removed) [remove c h 1 0 0 - 5 to avoid spamtrap]

 

[Dave Peterson]

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.

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 here:
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/microsoft.public.excel.misc/msg/d692c7e54
d fe909b?as_umsgid=eNXt5XX5BHA.1020@tkmsftngp07

Or

http://snipurl.com/121mv

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.

(e-mail address removed) [remove c h 1 0 0 - 5 to avoid spamtrap]