Listbox behavioural problems (Resizing)

[purplefox]

Hi,

I'm stuck at the moment with a problem with a listbox on a userform being a pain in the proverbial.

I'm using the RowSource method to populate the listbox (really only because I want proper column headers rather than labels above, although I might revert)

The listbox loads up ok, but then there are a couple of filter options, (well 1, the other is to show all). I'm using 2 radio buttons to filter, select the first and the listbox updates to only show Items (column headers) that don't match. To achieve this, when the listbox is first populated excel compares two sets of column headers and puts them onto a defined sheet to create a simple table, to the right it also adds just the non matching items to create the filtered table. When Filter option 1 (only show items to check) is selected, the RowSource range is moved from the complete table to the only check items table, this works fine, updates to only show the items that don't match.
The other filter option then just shifts the RowSource table back to the original one.

The problems that I then was having with this are
1. after filtering and then un-filtering the list I found that the last item could not be viewed as the scroll bar wouldn't go down that last bit to show the last item.
2. Clicking between the filters would make the listbox height change, get smaller.

Neither of these things are acceptable so I have been trying to find solutions to get it to work correctly.
I have tried various combinations of

1. enabling and un-enabling Integral Height and trying to force it back to the original height (it most the time seems to ignore my instruction to change to a specified height, or something is overwriting it
2. setting Me.ListBox1.MultiSelect = fmMultiSelectExtended then back to fmMultiSelectSingle
3. setting the height of the listbox to be a multiple of the font size (maybe the column heads confuse this?)

while the problem with being able to see the last item seems to get fixed with some combinations, the size of the listbox doesn't seem to want to stay put. I'm thinking of going back to the .add method of loading the listbox but it's annoying as then can't use the column headers. I've had this issue with command buttons madly changing size when a macros run before, but I think usually they would be ok if you specified the size and position in the macro, just doesn't seem to be working in this case.

Any help would be very welcome

thanks

Andy

 

[JEC]

Go check your resolution settings of your PC. You probably need to change it

 

[purplefox]

Go check your resolution settings of your PC. You probably need to change it

Hi,

thanks for the reply, I'm not sure how the resolution settings could be causing this issue else surely it would be causing the same effect on earlier versions of my userform before I was using RowSource.

 

[JEC]

I ran into this problem before. Some PC's had this problem, some of them didn't.
After I changed the resolution settings it worked again.

Note: never use rowsource to populate your controls. Also Additem is not the best way.
You can directly populate your listbox with: listbox1.list = range("A1:C10").value

 

[purplefox]

I ran into this problem before. Some PC's had this problem, some of them didn't.
After I changed the resolution settings it worked again.

Note: never use rowsource to populate your controls. Also Additem is not the best way.
You can directly populate your listbox with: listbox1.list = range("A1:C10").value

Hi JEC,

thanks for the reply, I haven't found that changing resolutions settings has helped in this matter.
The problem seems to be "common" and I guess something in the code or a method causes it to behave oddly.

With the population methods, I have only been using RowSource to populate as it's the only way to use the inbuilt column headers function. (stupidly you can add "headers" and use other population methods but the headers are blank.

Using RowSource meant I needed to compromise by having a table created by the macro upon initialising the userform, if I'm not using RowSource I can use the AddItem without needing to have a table on a sheet, and for the amount of data its very quick.

thanks

Andy