I have a worksheet with 4 command buttons. I wrote a macro to copy the entire worksheet to a new workbook (along with the macros referring to the command buttons). However, once I have copied the worksheet, the name of the command buttons changed to "commandbutton1", "commandbutton2", ... etc. For that reason, it won't read the orginial macros anymore (because the codes were written for the buttons with the orginal names). How do I change them back to the orginal names?

Anson,

It would be helpful to see the code that does the copying. The code:

sheet1.copy

copies a sheet with button names intact.

hth,

Doug Glancy

"Anson" wrote in message
...
I have a worksheet with 4 command buttons. I wrote a macro to copy the
entire worksheet to a new workbook (along with the macros referring to the
command buttons). However, once I have copied the worksheet, the name of the
command buttons changed to "commandbutton1", "commandbutton2", ... etc. For
that reason, it won't read the orginial macros anymore (because the codes
were written for the buttons with the orginal names). How do I change them
back to the orginal names?

Dim cBtn as MSForms.CommandButton
vArr = Array("btn1", "btn2", "btn3", "btn4")
Dim b as OleObject

i = lbound(varr)
for each b in ActiveSheet.OleObjects
if type of b is msforms.commandbutton then
set cBtn = b.Object
cBtn.Name = vArr(i)
i = i + 1
end if
Next


--
Regards,
Tom Ogilvy



"Anson" wrote in message
...
I have a worksheet with 4 command buttons. I wrote a macro to copy the
entire worksheet to a new workbook (along with the macros referring to the
command buttons). However, once I have copied the worksheet, the name of the
command buttons changed to "commandbutton1", "commandbutton2", ... etc. For
that reason, it won't read the orginial macros anymore (because the codes
were written for the buttons with the orginal names). How do I change them
back to the orginal names?

Not in Excel 97. The names are changed to default names. Fixed in Excel
2000 and later.

--
Regards,
Tom Ogilvy

"Doug Glancy" wrote in message
...
Anson,

It would be helpful to see the code that does the copying. The code:

sheet1.copy

copies a sheet with button names intact.

hth,

Doug Glancy

"Anson" wrote in message
...
I have a worksheet with 4 command buttons. I wrote a macro to copy the
entire worksheet to a new workbook (along with the macros referring to the
command buttons). However, once I have copied the worksheet, the name of
the
command buttons changed to "commandbutton1", "commandbutton2", ... etc.
For
that reason, it won't read the orginial macros anymore (because the codes
were written for the buttons with the orginal names). How do I change them
back to the orginal names?

Thanks, Tom.

Doug

"Tom Ogilvy" wrote in message
...
Not in Excel 97. The names are changed to default names. Fixed in Excel
2000 and later.

--
Regards,
Tom Ogilvy

"Doug Glancy" wrote in message
...
Anson,

It would be helpful to see the code that does the copying. The code:

sheet1.copy

copies a sheet with button names intact.

hth,

Doug Glancy

"Anson" wrote in message
...
I have a worksheet with 4 command buttons. I wrote a macro to copy the
entire worksheet to a new workbook (along with the macros referring to
the
command buttons). However, once I have copied the worksheet, the name of
the
command buttons changed to "commandbutton1", "commandbutton2", ... etc.
For
that reason, it won't read the orginial macros anymore (because the
codes
were written for the buttons with the orginal names). How do I change
them
back to the orginal names?

Hi Tom,

Thanks for you help!

"Tom Ogilvy" wrote:

Dim cBtn as MSForms.CommandButton
vArr = Array("btn1", "btn2", "btn3", "btn4")
Dim b as OleObject

i = lbound(varr)
for each b in ActiveSheet.OleObjects
if type of b is msforms.commandbutton then
set cBtn = b.Object
cBtn.Name = vArr(i)
i = i + 1
end if
Next


--
Regards,
Tom Ogilvy



"Anson" wrote in message
...
I have a worksheet with 4 command buttons. I wrote a macro to copy the
entire worksheet to a new workbook (along with the macros referring to the
command buttons). However, once I have copied the worksheet, the name of the
command buttons changed to "commandbutton1", "commandbutton2", ... etc. For
that reason, it won't read the orginial macros anymore (because the codes
were written for the buttons with the orginal names). How do I change them
back to the orginal names?