In Excel, how can I set up a VBA ComboBox so that it still works if the worksheet is copied? -

in excel 2010, can create activex combobox in worksheet , configure give me list of worksheets, , activate whichever worksheet select.

however, if copy worksheet containing combobox, new combobox dead. have duplicate vba code makes work, changing labels accordingly.

is there way set works automatically if copy worksheet?

this how i'm doing it:

microsoft excel objects \ thisworkbook:

private sub workbook_open()      ' rebuild list of sheets worksheet combobox.     dim long     = 1 thisworkbook.sheets.count         sheet1.combobox1.additem sheets(i).name         next  end sub 

microsoft excel objects \ sheet1(sheet1):

private sub combobox1_change()      sheet1.combobox1         sheets(.list(.listindex)).activate     end  end sub 

do in workbook module:

private sub workbook_open()     call populateboxes(sheet1) end sub 

in standard module, this:

sub populateboxes(ws worksheet)      dim sht worksheet      'populate combobox on sheet 1     dim obj     set obj = ws.oleobjects.item("combobox1").object     obj.clear     each sht in thisworkbook.worksheets         obj.additem sht.name     next  end sub 

then, in sheet1 module, make this:

private sub combobox1_change()      me.combobox1         sheets(.list(.listindex)).activate     end  end sub  private sub worksheet_activate()     call populateboxes(me) end sub 

now, code each combobox should functional after copying sheets.