Re-Sizing Control Boxes

[MazdaRacnChic]

How do I get my drop-down menu to re-size to be the same size as the cell in which it is placed? I am creating a worksheet with several different drop-down menus, and I want only the chosen option to appear within the cell. Please Help!

 

[Damon Ostrander]

Hi Racing,

If you are currently using ActiveX comboboxes from the Controls Toolbar to provide your in-cell dropdown, I suggest that you switch to using cell Data > Validation with a List instead. This provides a dropdown which automatically sizes with the cell, is very easy to use and set up, and requires much less memory than a combobox control. To use validation to get an in-cell dropdown, simply select the cell or cells, go to Data > Validation > select List in the Allow dropdown, and in the Source refedit box either type your list separated by commas, or enter a referenct to a range of cells that contains your list.

 

[pmdown]

I use This code trigered to doubleclick in the cell:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
' ComboBox1 must exist on The sheet and be hidden...
Set cb = ComboBox1
ComboBox1.ListFillRange = "Zoznam!2:c100"
' Set position and size of ComboBox1
cb.Width = Target.Width
cb.Height = Target.Height
cb.Top = Target.Top
cb.Left = Target.Left
' Shows combobox
cb.Visible = True
End Sub