Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to write in a dropdown data(Validation) | Excel Discussion (Misc queries) | |||
Data validation with dropdown list | Excel Discussion (Misc queries) | |||
Dropdown lists from datavalidation. | Excel Discussion (Misc queries) | |||
Dynamic Data Series Selection from Data Validation Dropdown? | Charts and Charting in Excel | |||
blanks in data validation list dropdown | Excel Worksheet Functions |