title: In Excel, how can I set up a VBA ComboBox so that it still works if the worksheet is copied?
body: <p>In Excel 2010, I can create an ActiveX ComboBox in a worksheet and configure it to give me a list of all worksheets, and it will activate whichever worksheet I select.</p>
  
  <p>However, if I copy the worksheet containing the ComboBox, the new ComboBox is dead.  I have to duplicate all the VBA code that makes it work, changing the labels accordingly.</p>
  
  <p>Is there any way to set it up so that it works automatically if I copy the worksheet?</p>
  
  <p>This is how I'm currently doing it:</p>
  
  <p><strong>Microsoft Excel Objects \ ThisWorkbook:</strong></p>
  
  <pre><code>Private Sub Workbook_Open()
  
      ' Rebuild the list of sheets for the worksheet ComboBox.
      Dim i As Long
      For i = 1 To ThisWorkbook.Sheets.Count
          Sheet1.ComboBox1.AddItem Sheets(i).Name
          Next
  
  End Sub
  </code></pre>
  
  <p><strong>Microsoft Excel Objects \ Sheet1(Sheet1):</strong></p>
  
  <pre><code>Private Sub ComboBox1_Change()
  
      With Sheet1.ComboBox1
          Sheets(.List(.ListIndex)).Activate
      End With
  
  End Sub
  </code></pre>