Finding window handle of data validation listbox

[PastorMike]

To widen the display of the data validation drop down, I could
SendMessage(lExcelDataValidationComboboxWindowHandle, CB_SETDROPPEDWIDTH,
cWidth, 0), but to do so I need the handle of the dropdown.
I can get as far as the handle of the Excel workbook window with the
technique described by Bullen, Bovey and Green, but I don't know what
classname to iterate with lExcelDataValidationComboboxWindowHandle =
FindWindowEx(hWorkbookWindowHandle, 0, "classname", vbNullString) on that
window to find the drop down. Is it a combobox? Or something else?
Thanks,
Mike Gardner

[NickHK]

Mike,
AFAIK these objects and worksheets controls do not expose a handle. Excel
draws them onto the parent worksheet when required. As such you're not going
to achieve your aim that way.
may be you could fake the drop down with one of your own combobox that sits
over the cell in question but has its .ListWidth set to some suitably large
number.

NickHK

"PastorMike" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
> To widen the display of the data validation drop down, I could
> SendMessage(lExcelDataValidationComboboxWindowHandle, CB_SETDROPPEDWIDTH,
> cWidth, 0), but to do so I need the handle of the dropdown.
> I can get as far as the handle of the Excel workbook window with the
> technique described by Bullen, Bovey and Green, but I don't know what
> classname to iterate with lExcelDataValidationComboboxWindowHandle =
> FindWindowEx(hWorkbookWindowHandle, 0, "classname", vbNullString) on that
> window to find the drop down. Is it a combobox? Or something else?
> Thanks,
> Mike Gardner
>

[PastorMike]

Nick,
Thanks for this insight. It's a bit frustrating to have searched so long
for this bit of UI tuning and have it elude me.

It is all the more so for knowing it can be done. The data validation is
not, as some have stated, limited to the width of the column in which the
validation is placed. In fact, the drop box width for all columns containing
data validation on a worksheet is the same, and is the width of the widest
column containing a data validation. This leads to an inconvenient
workaround whereby one can set a wide column offscreen, set a data validation
in it, save the workbook, and then upon reopening the drop width is wide for
all columns. This is undesirable for another reason also -- if one column's
validation list data is very short, you have the comic display of tiny
selections far away at one end of a huge box.

There is yet another workaround (besides overlaying with a combobox
control). It is possible to gain access to the "Drop Down nn" shape by
iterating the Shapes collection, and modify its width property
(drpShp.DrawingObject.ShapeRange.Width). The problem with this is the shape
then extends over adjacent columns and can be mistakenly selected by clicking
on what one assumes is a cell one or two columns over.

But the simple, direct solution would be to know where that default
CB_SETDROPPEDWIDTH property is stored and be able to poke it with the desired
value. It is there. I'm convinced there's some accessor to it. I just need
to find it.

Is there a way to find the window handle of an object in the Shapes
collection?


"NickHK" wrote:

> Mike,
> AFAIK these objects and worksheets controls do not expose a handle. Excel
> draws them onto the parent worksheet when required. As such you're not going
> to achieve your aim that way.
> may be you could fake the drop down with one of your own combobox that sits
> over the cell in question but has its .ListWidth set to some suitably large
> number.
>
> NickHK
>