Listbox size shrinks when items are loaded

[efernandes67]

Hi,

See attached file.

I have a form with two listboxes 'Available' and 'Selected'.
When I run the macro LoadList, the names of the named range 'myList' are loaded into the 'Selected' listbox, but the size of the listbox shrinks!

What can I do, so both listsboxes remain the same size?


Regards,
Elio Fernandes

[Norie]

Elio

When I try and run LoadList I get a compile error.

I can get it to run if I change Private to Dim for the variable declarations.

However neither listbox changes size.

[efernandes67]

Hi Norie,

I just replaced the attached file. The file before, had 'Private' in procedure instead of 'Dim'.
Can you check it again?

[Norie]

Eh, I've already tried changing Private to Dim, see my previous post.

I've tried the new file though and still no change in the size of the listboxes.

[efernandes67]

Norie,

I just placed the excel file in another computer and the right listbox shrinks. See png file.
You can see that the right listbox is more distant from OK button than the left listbox is from Cancel button.

[Norie]

What exactly do you mean 'shrunk'?

The 2 listboxes look exactly the same size to me, all the items from the worksheet are listed in the listbox on the right.

[efernandes67]

See the png file in my last post.

You can see that the right listbox is more distant from OK button than the left listbox is from Cancel button

[Norie]

Ah, looked at a bigger image and I see that.

Not happening when I open the userform though, see attached.

Is there any other code?

[efernandes67]

Please run the macro LoadList and show me the image with the items loaded.

[Norie]

I've run the sub LoadList every time to open the form.

Here's a picture.

[efernandes67]

The right listbox is empty!!! As you saw in my png file, there were some items loaded. Can you explain it to me?

[Norie]

The problem is that VBA will try to resize the listbox so it doesn't show partial characters.

I found the solution here and more info the help files.

PS Why don't you use the userform's Initalize event to populate the listboxes?

[efernandes67]

Thanks Norie, I used the the code in the link that you post it and it is working.

I am not using userform's Initalize to populate the listboxes, because I am using the same userform in different situations in the same file. What I mean, is that I am using this userform as a template.

Elio Fernandes