Macro trying to open second Personal?

[Ed]

Suddenly, a macro I use just fine yesterday says I can't open Personal.xls,
because it's already open! Of course it's already open - that's where my
macros are! I can choose the same macro from the list under Tools, and it
runs - but if I click the Custom Button on the toolbar, I get the error.
Any hints?

Ed

 

[Bob Phillips]

Re-assign the button to the macro.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ed]

Bob:

As I was fishing around for what happened and do the re-assigning, I noticed
the address for my macro somehow got changed from \Application
Data\Microsoft\Excel\XLSTART\Personal.xls to Desktop\Personal.xls. I
checked another button and , of course, it was changed, too!

I have no idea how this happened! Do you know of an easy way to reset this,
other than button by button?

Ed

 

[Bob Phillips]

No, it's one by one as far as I can see.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ed]

Well, here's to a l-o-n-g weekend! <clunk!>

Ed

No, it's one by one as far as I can see.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Open a bottle, and start positively

Bob

 

[Tom Ogilvy]

Adjust this code posted previously by Bernie Deitrick to replace wrong path
with the right path

JB,

Try running the macro below to change the link from the .xls to the .xla
file. You can also change the path by modifying the code, but as written
this assumes both files are in the same folder.

HTH,
Bernie
MS Excel MVP

Sub RepairUserDefinedButtons3()
Dim CmdBar As CommandBar
Dim i As Integer
On Error GoTo ErrorReading:

For Each CmdBar In CommandBars
For i = 1 To CmdBar.Controls.Count
If CmdBar.Controls(i).BuiltIn = False Then
If InStr(1, CmdBar.Controls(i).OnAction, _
"my-macros.xls") Then
CmdBar.Controls(i).OnAction = _
Replace(CmdBar.Controls(i).OnAction, _
"my-macros.xls", "my-macros.xla")
End If
End If
ErrorReading:
Next i
Next CmdBar
End Sub

 

[Ed]

Tom: This was great! Except I keep getting a "Memory this at that location
could not be read" error at

If CmdBar.Controls(i).BuiltIn = False Then

It kills the whole app but leaves a trace open; I have to shut that off with
Task Mangler. The only things I changed were the three strings and, in case
of crashes, I added ActiveWorkbook.Save after Next i. Did I set something
up wrong?

Ed

 

[Ed]

Okay - Got it to go through. I did an If CmdBar.Visible = True to constrain
to only the ones that are open - they're the only ones I have buttons on. I
also stepped through it with F5 and a breakpoint inside the If False; I
think maybe the looping was trying to overrun the saving (although I got the
issue before I put the Save in, too??).

Oh, well, it's done. Thank you much, Tom.

Ed