Resize Listbox with Function

[bdb1974]

Hi all,

I would like to use the following function to resize a listbox.

Is it possible that this can be modified to run in VBA?

Please Login or Register  to view this content.

Code found at: http://www.freevbcode.com/ShowCode.Asp?ID=614
Any help is appreciated.

Thanks,

BDB

[Leith Ross]

Hello BDB,

Visual Basic exposes the window handles of its objects while VBA does not. The window handle, abbreviated in the API as hWnd, is important in controlling an objects properties and events. While it is possible to access the window handle of an object in VBA on a UserForm, the code is very complex and often not stable. While it is a nice idea, in VBA it is not practical to implement.

[bdb1974]

Thanks Leith,

With that being said, I still would like to use some method to resize the list.

So, I've attempted to put together a function which seems to work to some extent. I have a few problems with it which I hope I can get resolved.

Here's what I have, (along with commented out lines which are my holdbacks)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

In my code, I set LB to listbox2 which is on sheet(1).

If I try and carry LB to my Function , LB.name will show LB's name, but if if say
LB.count it will error. Is there a way I can use my named object (LB) withing the Function other than just identifying the name?

Once my Function does the calculation to listbox adjustment it returns a value to plug in
which set the proper height. However, I'm still not always getting the height correctly adjusted for the number of items in the list. I don't know if it because my list is being built with directory names?

Anyways any help is much appreciated.

Thanks,

BDB

[bdb1974]

For anyone wondering why I'm wanting to resize my list box. It's because with my mouse over event, the spacing distance (from the bottom of my last item to the bottom of listbox itself) is the spacing distance my mouse pointer is from being over the item being selected. If the box is adjusted to the size needed for the number of items in it, this
weird phenomenon doesn't occur.