Fix Combo Box Sizing in Excel 2010

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.

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.

datavalcomboboxfix01

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.

datavalcomboboxfix02

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.

designmode

To select the ComboBox, type its name in the Name Box, and press Enter

datavalcomboboxfix03

On the Ribbon, under Drawing Tools, click Format, and click the Dialog Launcher for the Size group.

datavalcomboboxfix04

In the Format Shape dialog box, in the Size category, remove the check mark for Lock Aspect Ratio, and click OK

datavalcomboboxfix05

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.

___________

You may also like...

2 Responses

  1. 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 :)

  2. Ricardo says:

    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

Leave a Reply to Ricardo Cancel reply

Your email address will not be published. Required fields are marked *