Excel – Can the data validation list width be controlled in Excel 2007 and greater

microsoft excelmicrosoft-excel-2007vba

In Excel 2007, I can add data validation to a cell, and supply a list of allowed values.

However, experimentation suggests that the data validation drop-down list will never display at a smaller width than the original width of its cell (at the point the workbook was opened).

So if the cell's column was too big and the user re-sized it, you end up with this sort of thing:

drop-down wider than the column

Is there any way to improve this? Can the drop-down width be made to match the column width without closing and re-opening the workbook? Writing some VBA is an option.

Best Answer

You're right, this can occur when you change the width after creating the dropdown, but also sometimes when there are merged cells present. VBA is the only option. Place this in the worksheet object and change the column number to the column your list is in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
   If Target.Column = 4 Then
       Target.Columns.ColumnWidth = 20
   End If
End Sub

It will look for changes in your list and adjust to the specified width.

Related Solutions

Excel 2007 Data Validation: Validate Against List or Allow User to Enter Custom Value

You can do this with Excel's built-in Data Validation feature. It will work as an in-cell dropdown which will also allow you to type in a value not included in the dropdown. You'll need to do the following:

Somewhere in the workbook, enter the data you want to appear in the dropdown. In my example, it's on the same sheet as the dropdown, but it doesn't need to be.
Select the cell where you want to place the dropdown. Then, on the 'Data' ribbon, select 'Data Validation'. From there you will need to choose to validate your data from a list. Enter the address of the source data you created in Step 1.
Click the 'Error Alert' tab in the Data Validation dialog. Then, untick the 'Show error alert after invalid data is entered' checkbox. Click 'OK'.

Excel – How to have Excel data validation display different data in drop down than is actually validated

The only way I see would be to remove the data validation, and code your own drop down box.

The advantage of this is that the drop down will hide the actual cell, so the cell itself can still be edited as normal.

This code (from here) will add a drop down, and when an item is selected, put that value in the cell, change another cell based on the item selected, and then remove itself so no drop down exists. You should be able to use this code for your own fun.

Code reproduced here, just in case the link breaks:

Option Explicit

Sub Test()
    AddDropDown Range("D4")
End Sub

Sub AddDropDown(Target As Range)
    Dim ddBox As DropDown
    Dim vaProducts As Variant
    Dim i As Integer

    vaProducts = Array("Water", "Oil", "Chemicals", "Gas")
    Set ddBox = Sheet1.DropDowns.Add(Target.Left, Target.Top, Target.Width, Target.Height)
    With ddBox
        .OnAction = "EnterProductInfo" ' name corrected
        For i = LBound(vaProducts) To UBound(vaProducts)
            .AddItem vaProducts(i)
        Next i
    End With
End Sub

Private Sub EnterProductInfo()
    Dim vaPrices As Variant

    vaPrices = Array(15, 12.5, 20, 18)
    With Sheet1.DropDowns(Application.Caller)
        .TopLeftCell.Value = .List(.ListIndex)
        .TopLeftCell.Offset(0, 2).Value = vaPrices(.ListIndex - Array(0, 1)(1))
        .Delete
    End With
End Sub

Best Answer

Related Solutions

Excel 2007 Data Validation: Validate Against List or Allow User to Enter Custom Value

Excel – How to have Excel data validation display different data in drop down than is actually validated

Related Question