List Box Issues

[davegugg]

Hi

I have a user form I am trying to create. I will have two list boxes. One will show the names of my sheets. The other will hold the names of the sheets I want printed. I want to be able to click a sheet or sheets from SheetsListBox and move them over to PrintListBox with a command button. There will also be a button for moving all sheets to PrintListBox, Removing Select sheets from PrintListBox, or Removing All Sheets from PrintListBox.

Thus far, this is my code to fill in the SheetList Box, which I have called "AvailableListBox":

Please Login or Register  to view this content.

When I run this the form pops up, but nothing is in either list box and none of the buttons work. There is no VBA error, just nothing works.

Here is my code for my buttons:

Please Login or Register  to view this content.

I have a feeling Item.Select will not work so if anyone has any tips, I'd be more than grateful!

Thanks

[royUK]

There's a userform example here

[davegugg]

I don't see anything about userforms there. Did you mix up the link?

[royUK]

Post #7 of that thread

[BigBas]

In your first code, change the following line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

That should populate the listbox. Now see if the rest of the code works.

[davegugg]

Thanks, that helped a bunch, I think I have it just about ironed out. However, the form still isn't initializing properly. Do you think you could take a look? I posted an example:

Thanks!

[Kenneth Hobson]

You have several issues. The main thing, use the events of Userform, not an event that you add by typing. If you can not add them from the drop down list in the VBE, then they won't be available unless you are doing Class methods.

Please Login or Register  to view this content.

When working with listbox controls, remember the list items are 0 based. So ListCount-1 is the last index number.

[davegugg]

Ok, I see how those drop down lists work. I've changed everything I had over and now the list populates right. I have the buttons to move items into the printlistbox, but my buttons to move items out doesn't work.

Please Login or Register  to view this content.

The problem is I can't do for i = 1 to Me.PrintListBox.ListCount because when I remove an item the listcount goes down and it will look for a list item that doesn't exist at the end. I'd rather do For Each like I have above, but what I have above isn't right. How can I say for each item in the list?

Thanks!

[BigBas]

You can still use the i=1 style loop if you are accustomed to it, just work backwards. See the example below:

Instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

Understanding how to use loops backwards will save you headache in the future.

[davegugg]

That is helpful, but is there a way to do it with For Each? It seems to me like it would be cleaner.

Thanks!

[BigBas]

You can use FOR EACH, but you will no longer be working with LISTCOUNT/NUMBERS.

The FOR EACH loop works with parts of a collection. In this case, it is the ITEMS collection within the listbox, so your loop will look like:

Please Login or Register  to view this content.

Note: itm is a variable and should be declared

[Kenneth Hobson]

I think reverse iteration is your best route.

If you double-click your controls, you can get to the right object. Basically, don't type control names or events. While in say the click event for a control, you can select and another event and everything will be added for you.

Please Login or Register  to view this content.

[davegugg]

Hey, thanks all for your help, Kenneth, BigBas, and RoyUK. It now works great. This was my second try using a userform, and first with list boxes, and I've learned a lot. My final code is:

Please Login or Register  to view this content.

Thanks again everyone!