Button controls on worksheets

A

Anson

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?
 
D

Doug Glancy

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 said:
I have a worksheet with 4 command buttons. I wrote a macro to copy the
Click to expand...
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?
 
T

Tom Ogilvy

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 said:
I have a worksheet with 4 command buttons. I wrote a macro to copy the
Click to expand...
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?
 
T

Tom Ogilvy

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

Anson

Hi Tom,

Thanks for you help!

Tom Ogilvy said:
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




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?
Click to expand...
 
Top