With Excel data validation, you can create drop down lists on a worksheet. However, the font size is very small, and can’t be adjusted, and you can only see 8 items at at time.
ComboBox on Worksheet
With Excel VBA programming, you can add a ComboBox to the worksheet, to show the data validation list.
In the ComboBox, you can control the font size and the number of visible items in the list.
Problems in Excel 2010
Although this technique works nicely in Excel 2007, and earlier versions, you might have a problem with the ComboBox size in Excel 2010.
In the screen shot below, the ComboBox is about 1/4″ wide, instead of filling the entire cell.
In other workbooks, the ComboBox is so narrow that you can’t see it at all. That’s not too helpful a feature!
Fix the Problem in Excel 2010
Fortunately, the problem is easy to fix in Excel 2010, if you follow these steps.
On the Developer tab, click the Design Mode command.
To select the ComboBox, type its name in the Name Box, and press Enter
On the Ribbon, under Drawing Tools, click Format, and click the Dialog Launcher for the Size group.
Format Shape Dialog Box
In the Format Shape dialog box, in the Size category, remove the check mark for Lock Aspect Ratio, and click OK
That should fix the ComboBox sizing problem!
Watch the Combo Box Sizing Video
To see the steps for changing the Size setting in Excel 2010, you can watch this short Excel video tutorial.
___________
To do this in VBA use:
If Application.Version >= 14 Then cbo.ShapeRange.LockAspectRatio = msoFalse
Where cbo is the ComboBox object.
Hopefully this will help others searching the Excel Object Model and not finding it. I couldn’t find it so I guessed and got lucky 🙂
Sub MacroComboBox()
‘
‘ ComboBoxsize Macro
‘
‘
ActiveSheet.Shapes.Range(Array(“ComboBox1”)).Select
ActiveSheet.Shapes(“ComboBox1”).Height = 21.6
ActiveSheet.Shapes(“ComboBox1”).Width = 223.2
ActiveSheet.Shapes(“ComboBox1”).Height = 21.6
ActiveSheet.Shapes(“ComboBox1”).Width = 223.2
Range(“A1”).Select
End Sub