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

Question

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:

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.

Solution 1

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.

Solution 2

I can't say whether this will work for Excel 2007, but I just encountered this same problem in Excel 2013 and identified one possible cause for this situation, which naturally led to a solution.

TL;DR: Click once in the cell with a too-wide dropdown, save the workbook, then open the dropdown again.

---

Background: I made a time sheet workbook for myself because my department tracks labor hours by project and task, so I have a dozen rows on my time sheet to fill in. I used to just write in Notepad whenever I started and stopped work on a particular task, but since some tasks would have multiple short spans throughout the day, I created this workbook where I would put the same information in, but into a table, with each day having a separate worksheet, and then use a PivotTable to consolidate those five worksheets into one that is in the same layout as the timesheet I have to submit. On each of the daily worksheets, the table has validation for the end time (start time just equals the end time of the previous row) and task name, each of which target a range of cells on another sheet for the list of valid entries. The time columns are, obviously, much narrower than the task column.

---

Normally, the validation drop down boxes match the width of the column in which they lie, but one time I noticed that on just the Tuesday worksheet, the drop down was ridiculously wide. I was initially confused, because I had created each of the Tuesday through Friday worksheets by just copying the Monday one; there should have been no reason for there to be any difference between Tuesday and Wednesday.

It took me a couple of weeks before I finally figured out what was causing it (the first clue was that which sheet gave me too-wide dropdowns changed at one point), but eventually I realized:
On each worksheet in which a cell in the wider Task column was the active cell when I last saved the workbook, the narrower End Time column would give me a very wide dropdown - specifically, the same width as the Task column's dropdown!

From this, it was extremely easy to find the solution:
Activate a cell in the narrowest column with drop-down validation (on every worksheet that has them), then save the workbook.

(On further experimentation, I found that saving with the active cell being one without any validation worked equally well.)