Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Personal Macro Workbook | Excel Discussion (Misc queries) | |||
Personal macro workbook and personal.xls | Excel Discussion (Misc queries) | |||
How do I get my personal macro worksheet to open whenever I open . | Excel Discussion (Misc queries) |