Button controls on worksheets

[Guest]

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?

 

[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

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?

 

[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

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?

 

[Tom Ogilvy]

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

 

[Doug Glancy]

Thanks, Tom.

Doug

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

 

[Guest]

Hi Tom,

Thanks for you help!

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?