title: Excel 2010 VBA: Auto width of DropDown combo
body: <p>In Excel 2010, I have a worksheet with a dropdown combo. The combo is not an Active X, it as an form control. The combo takes its values from a table, and the table is re-filled by a vba macro, so the length of the values can change. </p>
  
  <p>After updating the values at the table, I would like to re-size the width of the combo to the width of the longest value. I have searched a lot, but I can't find how to do it. I have found some examples, but I don't know how to translate them to my combo. For example, the following code (taken from <a href="http://www.ozgrid.com/forum/showthread.php?t=55098" rel="nofollow">http://www.ozgrid.com/forum/showthread.php?t=55098</a> ):</p>
  
  <pre><code>Dim iWidth As Double 
  ComboBox1.AutoSize = True 
  iWidth = 0 
  
  For i = 0 To ComboBox1.ListCount - 1 
      ComboBox1.ListIndex = i 
      If iWidth &lt; ComboBox1.Width Then 
          iWidth = ComboBox1.Width 
      End If 
  Next 
  
  ComboBox1.Width = iWidth 
  ComboBOx1.AutoSize = False 
  ComboBox1.ListCount = 0 
  </code></pre>
  
  <p>would be translated to something like:</p>
  
  <pre><code>Dim iWidth As Double 
  Dim mycombo as DropDown
  Set mycombo = ActiveSheet.DropDowns("combo")
  mycombo.AutoSize = True     ' &lt;&lt;&lt;&lt; no 'AutoSize' property in DropDown class
  iWidth = 0 
  
  For i = 0 To mycombo.ListCount - 1 
      mycombo.ListIndex = i 
      If iWidth &lt; mycombo.Width Then 
          iWidth = mycombo.Width 
      End If 
  Next 
  
  mycombo.Width = iWidth 
  mycombo.AutoSize = False    ' &lt;&lt;&lt;&lt; no 'AutoSize' property in DropDown class
  mycombo.ListCount = 0 
  </code></pre>
  
  <p>but there is no 'AutoSize' property in DropDown class.</p>