Assignments to toolbar buttons changes with system changes
David McRitchie
and menus programmatically, or perhaps I'm not setting things up
correctly in the first place. I've corrected them manually, but
the fact that Excel doesn't just leave the assignment as
personal.xls but has to add in the complete pathname makes
this a problem.
When I changed my operating system from Win98 to Win2000,
the location that Office 2000 (Office 2K on both systems) has
for XLSTART changes which means I have to change the
macro assignments on each toolbar button.
Example I have to change:
'C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\xlstart\personal.xls'!ClearConstants
to personal.xls!ClearConstants
since the actual location is now actually
'C:\Documents and Settings\David McRitchie\Application Data\Microsoft\Excel\XLSTART\personal.xls'!ClearConstants
Slight digression:
This is somewhat related to wanting to create a menu spreadsheet
from an existing workbook's toolbars. In other words the reverse
of John Walkenbach's "MenuMaker", where you set up the
spreadsheet for the menus and then run "Menu Maker" to create
the menus. The reason is that I find it far easier to set up the
menus through Excel, but much easier to read a summary of
them in a spreadsheet.
Thanks in Advance,
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
Bernie Deitrick
The (rather old) sub below should help. I used it to correct the pathnames of my buttons when I had problems
upgrading. I used the Activecell statements to write out the old and new assignments to the worksheet to check the
transition, so those are optional (and I certainly wouldn't do it that way if I were writing this now <g>).
My incorrect path was C:\WINDOWS\Application Data\Microsoft\Excel\XLSTART\, and changing the path back to
C:\Excel\XLSTART\ cleared out the entire path. Let me know if this still works.
HTH,
Bernie
Sub RepairUserDefinedButtons()
Dim CmdBar As CommandBar
On Error GoTo ErrorReading:
For Each CmdBar In CommandBars
'If CmdBar.Visible = True Then
For i = 1 To CmdBar.Controls.Count
If CmdBar.Controls(i).BuiltIn = False Then
If InStr(1, CmdBar.Controls(i).OnAction, _
"\WINDOWS\Application") Then
ActiveCell.Value = CmdBar.Controls(i).OnAction
ActiveCell.Offset(1, 0).Select
CmdBar.Controls(i).OnAction = Replace(CmdBar.Controls(i).OnAction, _
"C:\WINDOWS\Application Data\Microsoft\Excel\XLSTART\", "C:\Excel\XLSTART\")
ActiveCell.Value = CmdBar.Controls(i).OnAction
ActiveCell.Offset(1, 0).Select
End If
End If
ErrorReading:
Next i
'End If
Next CmdBar
End Sub
David McRitchie
Thanks work like a charm, and I had to redo them
soon after posting question
because I lost my customization when I double
clicked instead of single clicked on the system
task bar to launch Excel and destroyed by toolbars
so had to reopen the old *.XLB file and start over.
Sub RepairUserDefinedButtons()
'mod. code of Bernie Deitrick, misc, 2002-04-16
'Fixes reassigned personal.xls
' that get modified when redoing systems or Excel versions
' macro saves having to change assigned macros on menus and toolbar buttons
' when the path to xlstart\personal.xls changes
Dim CmdBar As CommandBar
Dim Fc As Long 'Found in False Count
Fc = 0
On Error GoTo ErrorReading:
For Each CmdBar In CommandBars
For i = 1 To CmdBar.Controls.Count
If CmdBar.Controls(i).BuiltIn = False Then
' --- note my own personal.xls is named differently (D.McRitchie)
j = InStr(1, CmdBar.Controls(i).OnAction, "personal.xls'!")
If j > 0 Then
Fc = Fc + 1
ActiveCell.Offset(Fc * 2 - 1, 0).Value = i & " in " & CmdBar.Name
ActiveCell.Offset(Fc * 2 - 1, 1).Value = CmdBar.Controls(i).OnAction
ActiveCell.Offset(Fc * 2 - 1, 2).Value = CmdBar.Controls(i).Caption
ActiveCell.Offset(Fc * 2 - 1, 3).Value = CmdBar.Controls(i).ID
ActiveCell.Offset(Fc * 2 - 1, 4).Value = CmdBar.Controls(i).Type
ActiveCell.Offset(Fc * 2 - 1, 5).Value = CmdBar.Controls(i).TooltipText
CmdBar.Controls(i).OnAction = "'personal.xls'!" & _
Mid(CmdBar.Controls(i).OnAction, j + 14)
ActiveCell.Offset(Fc * 2, 1).Value = CmdBar.Controls(i).OnAction
End If
End If
ErrorReading:
Next i
Next CmdBar
End Sub
My own code is actually for pesonal.xls and +13 instead of
personal.xls and +14
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
"Bernie Deitrick" <dei...@consumer.org> wrote in message news:3CBC4A71...@consumer.org...
Jon Peltier
I have my personal.xls build and destroy the commandbars and menus
when Excel starts and quits. In fact, it's not called "personal.xls"
on my machine, either. But the syntax used in my code is like this:
MenuItem.OnAction = ThisWorkbook.name & "!" & NameOfMacro
"ThisWorkbook.name" takes care of path and filename automatically.
What I use is a customization of John Walkenbach's great menumakr.xls
utility, which I've jazzed up to use either a custom button face or
a built in one, depending on what I put into an extra column in the
worksheet.
- Jon
-------
Jon Peltier, Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
In article <eNXt5XX5BHA.1020@tkmsftngp07>, dmcri...@msn.com says...
news:3CBC4A71.25F32443@
David McRitchie
myself to keep track of duplicates, and some columns to
sort the menus as I want them perhaps some other things.
Nevertheless, I prefer to have the toolbars set up in Excel.
If I could create the spreadsheet for MenuMaker from the
existing toolbars, I would be a lot more compfortable with
it.
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
"Jon Peltier" <jonpe...@yahoo.com> wrote in message news:e9NI5To5BHA.616@tkmsftngp05...
David McRitchie
Actually my original concern was the toolbar buttons
which are not covered in MenuMaker anyway.
And Menumaker can be referencing macros
in multiple workbooks.
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
"Jon Peltier" <jonpe...@yahoo.com> wrote in message
David McRitchie
The changes to the toolbar buttons (in this thread) went
nicely, but I need help from someone for making the
changes of macro assignments for the menus and
submenus as well. I expect the code would be similar.
Bernie Deitrick
You simply need to check for the control being a popup, and then do the same
sort of checking. I know you've modified the code I originally posted, but
I went back to it to show the technique.
HTH,
Bernie
Sub RepairUserDefinedButtons2()
Dim CmdBar As CommandBar
Dim myControl As CommandBarControl
On Error GoTo ErrorReading:
For Each CmdBar In CommandBars
For i = 1 To CmdBar.Controls.Count
With CmdBar.Controls(i)
If .BuiltIn = False Then
MsgBox "Custom control: " & .Caption
If .Type = msoControlPopup Then
MsgBox "Control Popup"
For Each myControl In .Controls
MsgBox "Custom control on popup: " &
myControl.Caption
If InStr(1, myControl.OnAction, _
"\WINDOWS\Application") Then
myControl.OnAction = Replace(myControl.OnAction,
_
"C:\WINDOWS\Application
Data\Microsoft\Excel\XLSTART\", _
"C:\Excel\XLSTART\")
End If
Next
Else
If InStr(1, .OnAction, _
"\WINDOWS\Application") Then
.OnAction = Replace(.OnAction, _
"C:\WINDOWS\Application
Data\Microsoft\Excel\XLSTART\", _
"C:\Excel\XLSTART\")
End If
End If
End If
End With
ErrorReading:
Next i
Next CmdBar
End Sub
"David McRitchie" <dmcri...@msn.com> wrote in message
news:esYbL9x5BHA.2112@tkmsftngp02...
David McRitchie
Unfortunately this only does one level of menus,
which doesn't do much for me.
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
"Bernie Deitrick" <dei...@consumer.org> wrote in message news:uCRotQ05BHA.2004@tkmsftngp02...
Bernie Deitrick
You can check as many levels as you want: each of the controls on the popup
control can also be checked as to whether it is a popup or not, and all
controls beneath it can be tested, and on an on. (What's that line about
fleas having littler fleas.....)
I simply showed one level to illustrate the technique. I rarely go to a
second level, but you can build your code to test as many levels as you
have. You could probably build a recursively callable function to check each
control for sub-controls to simplify your code.
HTH,
Bernie
"David McRitchie" <dmcri...@msn.com> wrote in message
news:enWVZjB6BHA.2156@tkmsftngp05...
David McRitchie
Just diminished fixing my toolbars with help of posting by
Bill Manville, 1998/01/17, misc, involving recursive updating
of custom toolbars which needed only a slight modification
for the interface with the other subroutines in his posting.
Perhaps I should place my macros in
a non XLSTART library as you do, and then wouldn't
have to fix the references in the menus (.xlb) again.
Google Search arguments:
msoControlPopup sub recursion OR recursive OR function
"In CommandBars" -addmenu group:*excel*
Subject: Re: custom toolbars
http://groups.google.com/groups?selm=34bff0b7.86017579%40msnews.microsoft.com
Just had to change the first subroutine for proper interface:
Sub FixXLBpersonal()
'Bill Manville, 1998/01/17, recursive, updating custom toolbars
' http://groups.google.com/groups?selm=34bff0b7.86017579%40msnews.microsoft.com
' -- UpdateOnactions "C:\EXCEL\XLSTART", "C:\OFFICE97\XLSTART"
UpdateOnactions _
"'C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\xlstart\pesonal.xls'!", _
"'pesonal.xls'!"
End Sub
Thanks for pointing me in the right direction, and I think
all the key pieces neccessary to generate spreadsheet
entries from actual current toolbars suitable for
John Walkenbach's "Menu Maker"
http://www.j-walk.com/ss/excel/tips/tip53.htm
could be found in this thread.
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
"Bernie Deitrick" <dei...@consumer.org> wrote in message
> David,
> You can check as many levels as you want: each of the controls on the popup
> control can also be checked as to whether it is a popup or not, and all
> controls beneath it can be tested, and on an on. (What's that line about
> fleas having littler fleas.....)
>
> I simply showed one level to illustrate the technique. I rarely go to a
> second level, but you can build your code to test as many levels as you
> have. You could probably build a recursively callable function to check each
> control for sub-controls to simplify your code.
>
> "David McRitchie" <dmcri...@msn.com> wrote ...
David McRitchie
think blind (window obscured) use of spellcheck did it in.
For the benefit of those seaching for solutions this was
the tip off that something was wrong, and which I knew
to be incorrect macro assignments of toobar buttons
in this case.
-----
A document with the name 'personal.xls' is already open. You
cannot open two documents with the same name, even if the
documents are in different folders.
To open the second document, either close the document that's
currently open, or rename one of the documents.
[OK]
----
Fixed with macro by Bill Manville to recursively fix
the macro assignments. Thanks Bills, and again
to Bernie for helping me find what I was looking for.
See earlier parts of this thread.
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
"David McRitchie" <dmcri...@msn.com> ...