Data Validation Dropdown bug
 

Several weeks ago I posted a problem with my data validation dropdown lists
becoming much wider than the column they were attached to. They appeared to
be picking up the width of the wrong column. After weeks of trying to
determine the cause (in my spare time, of course) I was finally able to
reproduce the error and come up with a work-around.

I use several controls on my sheets to unprotect them, unlock cells, attach
data validation, re-protect the sheet, etc. in an effort to "guide" my users
into correct data entry. (If there's a way to enter garbage, there's a user
who will find it.) In other words, I create and delete data validation cells
on the fly.

I discovered that once I've created the DV cells, if the user clicks in a DV
cell of a wide column (even if the user does not select from that list) it
sets the width of the dropdown! Clicking in a wider DV cell after the first
DV cell works fine, but clicking in a narrower DV cell shows the list at the
same width as the first DV cell. It's really annoying when the first cell is
half a page wide and the next one is only a few characters wide!

My work-around, even though "Kludgey", is to pre-set the DV dropdown width
using a narrow "phantom" cell when the user clicks the control to unlock the
sheet. I save the active cell postion, then add DV to a narrow cell,
activate that cell, delete the DV, then re-activate the original cell. I
scan across row 1 for the narrowest column width. Here's a snippet:

iRow = ActiveCell.Row
iCol = ActiveCell.Column
Cells(1, iSmallestCol).Validation.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=A2"
Cells(1, iSmallestCol).Activate
Cells(1, iSmallestCol).Validation.Delete
Cells(iRow, iCol).Activate

It's stupid to have to do this but I couldn't figure out another way. There
is no DV Dropdown width property to set as far as I can tell.