Can I mass-change macro paths of tool buttons?

[Mel]

Excel v2003

I have many custom tool buttons on my tool bars that are assigned to
macros from my personal.xls. Upon getting a new computer, I restored
all those buttons using my old Excel11.xlb file and put my old
personal.xls back in the XLSTART folder. However, the path to my
personal.xls is slightly different on the new notebook.

It was:
C:\Documents and Settings\Melina\Application Data\Microsoft\Excel
\XLSTART\PERSONAL.XLS

It's now:
C:\Documents and Settings\Mel\Application Data\Microsoft\Excel\XLSTART
\PERSONAL.XLS

So, now my buttons won't work because they're looking for the old
path. I could change the path of each in the macro assignments, but is
there a way or code to mass-change the path to these macros or do I
have to change them one at a time?

Thanks for your advice.
-Mel

 

[paul.robinson]

Hi
Can't you just do Search...Replace All for all instances of this text
within the Personal.xls VB Project? (there is an option within Replace
to choose the entire project)
regards
Paul

 

[Peter T]

Close your Personal.
Rename the Personal in the new folder
Open the original Personal from the *old* folder
SaveAs Personal with same name but in the *new* folder.

If the two personal's are not the same, delete the recently SaveAs file and
restore the renamed Personal in the new folder (assuming of course the new
Personal has similarly named macros).

Regards,
Peter T

 

[Peter T]

Forgot to add, as normally Personal is hidden you may find it easier to do
your SaveAs and Close from the Immediate window, Ctrl-G

Make sure you select your Personal each time, type things like the following
and with the cursor at the end of the line hit Enter.

?thisworkbook.fullname

thisworkbook.saveas "new fullname"

thisworkbook.close

Peter T

 

[Mel]

No. The path to personal.xls is not entered as text in a module of the
personal.xls. When the macro is assigned to a button, the tool
button's property records the path to the macro workbook, in this case
the personal.xls, which was in ...\Melina\... and is now ...\Mel\... .
The tool button's property still looks for personal.xls in that
original path instead of the current path.

 

[Mel]

Each session of opens the personal.xls from the default path, which is
now ...\Mel\... , but used to be ...\Melina\... . As a workaround I
have created the old path and placed a copy of the personal.xls in it.
In Excel I can then close the default ...\Mel\...\personal.xls and
open ...\Melina\...\personal.xls and of course the buttons will then
work. That's because the personal.xls now open has the correct path as
in the properties of the buttons' assigned macros. But it's a mess to
have to close one and open the other each time I start Excel, not to
mention I may end up maintaining two personal.xls files.

Is this closing one and opening the other workaround what you were
suggesting?

 

[Peter T]

Is this closing one and opening the other workaround what

you were suggesting?

Open the file in the folder with the correct links (all buttons should
work). Then SaveAs the file in the new folder (all the buttons should still
work). Did you try following the steps I suggested last time.

Regards,
Peter T


Each session of opens the personal.xls from the default path, which is
now ...\Mel\... , but used to be ...\Melina\... . As a workaround I
have created the old path and placed a copy of the personal.xls in it.
In Excel I can then close the default ...\Mel\...\personal.xls and
open ...\Melina\...\personal.xls and of course the buttons will then
work. That's because the personal.xls now open has the correct path as
in the properties of the buttons' assigned macros. But it's a mess to
have to close one and open the other each time I start Excel, not to
mention I may end up maintaining two personal.xls files.

Is this closing one and opening the other workaround what you were
suggesting?

 

[Dave Peterson]

Saved from a previous post:

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)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

 

[Mel]

For some reason, I didn't get it the first time, but now I understand.
YES, it worked beautifully. Thank you! That saved me a ton of time!

-Mel

 

[Mel]

Thanks, Dave, but it didn't involve code making a toolbar such as an
addin or other routine involving lines of code. It was just having
assigned a blank tool bar button to a macro residing in my
personal.xls, and then the personal.xls workbook getting a new path
when changing to a new laptop, but still using the former XLB toolbar
file. The tool button (not the macro) couldn't find the personal.xls
with that path.

David T gave me the solution to open the old personal.xls and SaveAs
it in the new XLSTART location. Worked like a charm and saved me a
bunch of time from reassigning all those tool buttons.

-Mel

 

[Dave Peterson]

I wasn't addressing how you created the existing toolbar.

I was suggesting that you won't have any further problems if you created the
toolbar using code.

 

[Mel]

Oh, ok. I think I see what you're saying now. Sorry. I like your idea
because I've had the XLB file become corrupt before and had to rebuild
my toolbars before - not to mention the path problem I just had.

-Mel

 

[Dave Peterson]

And it's easier to share with others, too.

But don't name your addin personal.xl*. That name could be used by a lot of the
recipients.

 

[Mel]

How do you deal with the button faces, though? I know I can assign an
ID, but that's a limited library of faces. With making them manually,
I could create my own face - freehand or pasting a pic. Is there a way
to go beyond the limited ID numbers in code to make a custom face?

Thanks,
Mel

 

[Dave Peterson]

Saved from a previous post...

But if you could put the pictures on a worksheet and name them nicely, you could
use something like:

Option Explicit
Public Const ToolBarName As String = "MyToolbarName"
Sub Auto_Open()
Call CreateMenubar
End Sub
Sub Auto_Close()
Call RemoveMenubar
End Sub
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub
Sub CreateMenubar()

Dim iCtr As Long

Dim MacNames As Variant
Dim CapNames As Variant
Dim TipText As Variant
Dim PictNames As Variant
Dim PictWks As Worksheet

Call RemoveMenubar

MacNames = Array("aaa", _
"bbb")

CapNames = Array("AAA Caption", _
"BBB Caption")

TipText = Array("AAA tip", _
"BBB tip")

PictNames = Array("Picture 1", _
"Picture 2")

Set PictWks = ThisWorkbook.Worksheets("Pictures")

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For iCtr = LBound(MacNames) To UBound(MacNames)
PictWks.Pictures(PictNames(iCtr)).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNames(iCtr)
.Style = msoButtonIconAndCaption
.PasteFace
.TooltipText = TipText(iCtr)
End With
Next iCtr

End With
End Sub
Sub AAA()
MsgBox "aaa"
End Sub
Sub BBB()
MsgBox "bbb"
End Sub

 

[Peter T]

a limited library of faces

There are several thousand, limited?

Maybe you haven't seen them all, there are various utilities exit to display
them, eg Jim Rech's BtnFaces.zip
http://www.oaltd.co.uk/MVP/Default.htm

For professional purposes you may indeed want to make your own custom icons,
however I don't install any addins that have those, and load them only when
needed. Why - I often start Excel with something in the clipboard ready to
past in. I *always* forget that doing loading custom icons with CopyFace
clears the clipboard!

Regards,
Peter T


How do you deal with the button faces, though? I know I can assign an
ID, but that's a limited library of faces. With making them manually,
I could create my own face - freehand or pasting a pic. Is there a way
to go beyond the limited ID numbers in code to make a custom face?

Thanks,
Mel