title: VBA Excel combobox not displaying the value after selecting option
body: <pre><code>Private Sub ComboBox1_DropButtonClick()
  
      If ComboBox1.ListCount &gt; 0 Then
      ActiveSheet.ComboBox1.Clear
      End If
  
      For N = 1 To ActiveWorkbook.Sheets.Count - 1
      ComboBox1.AddItem ActiveWorkbook.Sheets(N).Name
      Next N
  
  End Sub
  </code></pre>
  
  <p>I'm new to VBA so please bear with me.  I may not be doing this the best way to begin with.</p>
  
  <p>The code is taking the names of each sheet in my workbook (with the exception of the last sheet) and adding them to a combobox list.  At first, each time I clicked the drop down, all sheet names were being added again making the list continue to grow with every click.  My remedy was to clear the combobox first on each click and repopulate.</p>
  
  <p>However, with the clear option being used, the value is not being displayed when making my selection.  It displays fine when not using the clear option.  Everything else still works, but I need it to show the selected value so users aren't confused.</p>
  
  <p>Is there a better way to accomplish what I need?</p>
  
  <p>EDIT: If it matters, this is not in a user form, it's just a active x combobox located directly on a worksheet.</p>