ExcelBanter - Widen Data Validation Dropdown

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Widen Data Validation Dropdown (https://www.excelbanter.com/excel-programming/284701-widen-data-validation-dropdown.html)

Widen Data Validation Dropdown

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

Widen Data Validation Dropdown

No.

--
Regards,
Tom Ogilvy

Darren Hill wrote in message
...
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

Widen Data Validation Dropdown

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

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

Darren Hill wrote:
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

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Widen Data Validation Dropdown

Lol, thought that might be the case. Thanks anyway :)
--
Darren
"Tom Ogilvy" wrote in message
...
No.

--
Regards,
Tom Ogilvy

Darren Hill wrote in message
...
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

Widen Data Validation Dropdown

Thanks, that's a great workaround. :)

--
Darren
"Debra Dalgleish" wrote in message
...
You could use a SelectionChange event to temporarily widen the column.
There are some instructions he

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

Darren Hill wrote:
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

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html