HELP! Macros point to wrong file!
 

I launched Excel and got a blank workbook. Actually, it was my
Personal.xls, but it was blank. So I typed in it and SavedAs a new name.
Now all my macros want the file I SavedAs, not Personal.xls!! I can
right-click on my toolbars to get Customize, then right-click on a macro
button to see the Assign(ed) Macro, and it's pointing to this other file. I
deleted it, and restarted, but it didn't help. All the modules are still in
Personal. How do I change back 34 modules?

Ed

HELP! Macros point to wrong file!
 

Loop through your commandbars and the controls on the commandbars, test the
onaction property to see if it contains a reference to the new file and if
so, change it back by modifying the string (onaction property value) and
assigning it back to the onaction property.

sPath = "C:\Program Files\Microsoft Office\Office\xlStart\Personal.xls"

'start of loop

if obj.onaction < "" then
if instr(1,.obj.onaction,"C:\My folder\myfile.xls",vbTextCompare) then
obj.onaction = Application.Substitute(obj.Onaction,"C:\My
Folder\myfile.xls",sPath)
end if
end if

next obj

where obj is a reference to your commandbar button.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
I launched Excel and got a blank workbook. Actually, it was my
Personal.xls, but it was blank. So I typed in it and SavedAs a new name.
Now all my macros want the file I SavedAs, not Personal.xls!! I can
right-click on my toolbars to get Customize, then right-click on a macro
button to see the Assign(ed) Macro, and it's pointing to this other file.
I
deleted it, and restarted, but it didn't help. All the modules are still
in
Personal. How do I change back 34 modules?

Ed

HELP! Macros point to wrong file!
 

Tom, thanks for the help. I am having some problems, though.

How do I set a reference to all my CommandBar Buttons?
Isn't "Next" the end of a loop? What starts the loop? What did I miss?

Ed

"Tom Ogilvy" wrote in message
...
Loop through your commandbars and the controls on the commandbars, test
the
onaction property to see if it contains a reference to the new file and if
so, change it back by modifying the string (onaction property value) and
assigning it back to the onaction property.

sPath = "C:\Program Files\Microsoft Office\Office\xlStart\Personal.xls"

'start of loop

if obj.onaction < "" then
if instr(1,.obj.onaction,"C:\My folder\myfile.xls",vbTextCompare) then
obj.onaction = Application.Substitute(obj.Onaction,"C:\My
Folder\myfile.xls",sPath)
end if
end if

next obj

where obj is a reference to your commandbar button.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
I launched Excel and got a blank workbook. Actually, it was my
Personal.xls, but it was blank. So I typed in it and SavedAs a new
name.
Now all my macros want the file I SavedAs, not Personal.xls!! I can
right-click on my toolbars to get Customize, then right-click on a macro
button to see the Assign(ed) Macro, and it's pointing to this other
file.
I
deleted it, and restarted, but it didn't help. All the modules are
still
in
Personal. How do I change back 34 modules?

Ed

HELP! Macros point to wrong file!
 

See where it says "start of loop". This is called pseudo code.

I don't know where your references are. Are they all on one commandbar.
Do you need to recursively search every button in Excel?

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
Tom, thanks for the help. I am having some problems, though.

How do I set a reference to all my CommandBar Buttons?
Isn't "Next" the end of a loop? What starts the loop? What did I miss?

Ed

"Tom Ogilvy" wrote in message
...
Loop through your commandbars and the controls on the commandbars, test
the
onaction property to see if it contains a reference to the new file and
if
so, change it back by modifying the string (onaction property value) and
assigning it back to the onaction property.

sPath = "C:\Program Files\Microsoft Office\Office\xlStart\Personal.xls"

'start of loop

if obj.onaction < "" then
if instr(1,.obj.onaction,"C:\My folder\myfile.xls",vbTextCompare) then
obj.onaction = Application.Substitute(obj.Onaction,"C:\My
Folder\myfile.xls",sPath)
end if
end if

next obj

where obj is a reference to your commandbar button.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
I launched Excel and got a blank workbook. Actually, it was my
Personal.xls, but it was blank. So I typed in it and SavedAs a new
name.
Now all my macros want the file I SavedAs, not Personal.xls!! I can
right-click on my toolbars to get Customize, then right-click on a
macro
button to see the Assign(ed) Macro, and it's pointing to this other
file.
I
deleted it, and restarted, but it didn't help. All the modules are
still
in
Personal. How do I change back 34 modules?

Ed

HELP! Macros point to wrong file!
 

Tom, thanks for trying. During the interim, I manually changed the
pointers. I didn't even know, though, what I was looking at to change until
I read through your code.

I think I really need to get some VBA training, or at east a VBA-specific
book. "Next" and where my buttons are shouldn't be hard to know, and
shouldn't require a personal expert to hold my hand. I sincerely appreciate
your help.

Ed

"Tom Ogilvy" wrote in message
...
See where it says "start of loop". This is called pseudo code.

I don't know where your references are. Are they all on one commandbar.
Do you need to recursively search every button in Excel?

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
Tom, thanks for the help. I am having some problems, though.

How do I set a reference to all my CommandBar Buttons?
Isn't "Next" the end of a loop? What starts the loop? What did I miss?

Ed

"Tom Ogilvy" wrote in message
...
Loop through your commandbars and the controls on the commandbars,
test
the
onaction property to see if it contains a reference to the new file
and
if
so, change it back by modifying the string (onaction property value)
and
assigning it back to the onaction property.

sPath = "C:\Program Files\Microsoft
Office\Office\xlStart\Personal.xls"

'start of loop

if obj.onaction < "" then
if instr(1,.obj.onaction,"C:\My folder\myfile.xls",vbTextCompare)
then
obj.onaction = Application.Substitute(obj.Onaction,"C:\My
Folder\myfile.xls",sPath)
end if
end if

next obj

where obj is a reference to your commandbar button.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
I launched Excel and got a blank workbook. Actually, it was my
Personal.xls, but it was blank. So I typed in it and SavedAs a new
name.
Now all my macros want the file I SavedAs, not Personal.xls!! I can
right-click on my toolbars to get Customize, then right-click on a
macro
button to see the Assign(ed) Macro, and it's pointing to this other
file.
I
deleted it, and restarted, but it didn't help. All the modules are
still
in
Personal. How do I change back 34 modules?

Ed