Macro trying to open second Personal?
 

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

Macro trying to open second Personal?
 

Re-assign the button to the macro.

--

HTH

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


"Ed" wrote in message
...
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

Macro trying to open second Personal?
 

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" wrote in message
...
Re-assign the button to the macro.

--

HTH

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


"Ed" wrote in message
...
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

Macro trying to open second Personal?
 

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

--

HTH

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


"Ed" wrote in message
...
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" wrote in message
...
Re-assign the button to the macro.

--

HTH

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


"Ed" wrote in message
...
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

Macro trying to open second Personal?
 

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

Ed

"Bob Phillips" wrote in message
...
No, it's one by one as far as I can see.

--

HTH

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


"Ed" wrote in message
...
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" wrote in message
...
Re-assign the button to the macro.

--

HTH

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


"Ed" wrote in message
...
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

Macro trying to open second Personal?
 

Open a bottle, and start positively :-)

Bob


"Ed" wrote in message
...
Well, here's to a l-o-n-g weekend! <clunk!

Ed

Macro trying to open second Personal?
 

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

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
Well, here's to a l-o-n-g weekend! <clunk!

Ed

"Bob Phillips" wrote in message
...
No, it's one by one as far as I can see.

--

HTH

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


"Ed" wrote in message
...
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" wrote in message
...
Re-assign the button to the macro.

--

HTH

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


"Ed" wrote in message
...
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

Macro trying to open second Personal?
 

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
Well, here's to a l-o-n-g weekend! <clunk!

Ed

"Bob Phillips" wrote in message
...
No, it's one by one as far as I can see.

--

HTH

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


"Ed" wrote in message
...
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" wrote in message
...
Re-assign the button to the macro.

--

HTH

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


"Ed" wrote in message
...
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

Macro trying to open second Personal?
 

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

"Ed" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
Well, here's to a l-o-n-g weekend! <clunk!

Ed

"Bob Phillips" wrote in message
...
No, it's one by one as far as I can see.

--

HTH

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


"Ed" wrote in message
...
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" wrote in
message
...
Re-assign the button to the macro.

--

HTH

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


"Ed" wrote in message
...
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