Hi everyone,

Say I created a macro I named "Base" in a an excel workbook named
ABC.xls

Later, say I changed the file name to DEF.xls

Now, when I click on the macro button to execute, I get "out of range"
error and the file close!

How can I make the macro name is independent of the file name; so it
doesn't keep referring to the old file name?

Regards,
Mike

You can't. The macro is contained in the file, so it is part of the
identification.

If you want to rename the file, open it in excel and rename it by doing a
file saveas.

--
Regards,
Tom Ogilvy

"Mike" wrote in message
ups.com...
Hi everyone,

Say I created a macro I named "Base" in a an excel workbook named
ABC.xls

Later, say I changed the file name to DEF.xls

Now, when I click on the macro button to execute, I get "out of range"
error and the file close!

How can I make the macro name is independent of the file name; so it
doesn't keep referring to the old file name?

Regards,
Mike

Mike,

Does your code look like this?

Workbooks("ABC.xls").Sheets("Sheet1").Range("A1"). Value=1

If so, change it to:

ThisWorkbook.Sheets("Sheet1").Range("A1").Value=1

ThisWorkbook will always return a reference to the workbook in which the VBA
code resides, so you don't have to worry about name changes.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Mike wrote:
Hi everyone,

Say I created a macro I named "Base" in a an excel workbook named
ABC.xls

Later, say I changed the file name to DEF.xls

Now, when I click on the macro button to execute, I get "out of range"
error and the file close!

How can I make the macro name is independent of the file name; so it
doesn't keep referring to the old file name?

Regards,
Mike

Jake read your post differently from me - and after rereading your post,
perhaps he is correct.

I saw button and thought you had assigned the macro to a command button or
other type of button.

So my comments would only be appropriate in that context.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
You can't. The macro is contained in the file, so it is part of the
identification.

If you want to rename the file, open it in excel and rename it by doing a
file saveas.

--
Regards,
Tom Ogilvy

"Mike" wrote in message
ups.com...
Hi everyone,

Say I created a macro I named "Base" in a an excel workbook named
ABC.xls

Later, say I changed the file name to DEF.xls

Now, when I click on the macro button to execute, I get "out of range"
error and the file close!

How can I make the macro name is independent of the file name; so it
doesn't keep referring to the old file name?

Regards,
Mike

Hi Tom,

Tom Ogilvy wrote:
Jake read your post differently from me - and after rereading your
post, perhaps he is correct.

I saw button and thought you had assigned the macro to a command
button or other type of button.

Hmmm....after reading this post and rereading the OP's post, I think you may
be the one who interpreted the question correctly. <g

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]