Widen Data Validation Dropdown

[Darren Hill]

I use Chip Pearson' very nifty WidenNameBox macro to resize the name box for
viewing longer defined names. Is it possible to modify this macro to work
with the Data Validation drop down?
It's frustrating at be limited to the width of the cell (which in my current
sheet is only 24 points), and using comboboxes wouldn't be ideal as I have
over 6000 rows and 5 columns which contain dropdowns.

The original macro is below.
Yours in possibly futile hope, Darren
--------------------------
The macro is:

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lParam As Any) As Long


Sub WidenNameBoxDrop2()

Dim Res As Long
Const CB_SETDROPPEDWIDTH = &H160
Const cWidth = 300 '<<<<<<<<<<<<<<<<<<<<<<
Res = SendMessage( _
FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "EXCEL;", vbNullString) _
, 0, "combobox", vbNullString), _
CB_SETDROPPEDWIDTH, cWidth, 0)

End Sub

 

[Tom Ogilvy]

No.

 

[Debra Dalgleish]

You could use a SelectionChange event to temporarily widen the column.
There are some instructions here:

http://www.contextures.com/xlDataVal08.html#Wider

 

[Darren Hill]

Lol, thought that might be the case. Thanks anyway

 

[Darren Hill]

Thanks, that's a great workaround.

--
Darren

You could use a SelectionChange event to temporarily widen the column.
There are some instructions here:

http://www.contextures.com/xlDataVal08.html#Wider