Button controls on worksheets
 

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?

Button controls on worksheets
 

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?

Button controls on worksheets
 

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?

Button controls on worksheets
 

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?

Button controls on worksheets
 

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?

Button controls on worksheets
 

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?