Auto select items in a Listbox with items from another Listbox

I have two ListBoxes, one is located on a worksheet and the other is in a Userform. The Listbox on the Userform has many items in the list, while the Listbox on the sheet only has some of those same items in the list. I am trying to have the Listbox in the Userform automatically "Select" all items that are also found in the Sheet's Listbox. I'm using the following code:

Please Login or Register  to view this content.

It works if there is just one item in the Sheet's Listbox, however, I get a "Run-time error '1004': Application-defined or object defined error" if there is more than one item in the Sheet's Listbox.

Any ideas how to overcome this error?

This selects all the items in the UserForm ListBox that exist in the Worksheet Listbox.

Please Login or Register  to view this content.

Originally Posted by perducci

I feel like it's so close! I'm getting a Type Mismatch error on this line:

Please Login or Register  to view this content.

By the way, occasionally the Sheet's Listbox won't have any items in it. Does that create a problem? I should also mention that the code is run upon activating Userform1. I don't know if that makes a difference.

Thank you

Dim v as type variant

Please Login or Register  to view this content.

The project I'm working on is very complex so I have included a simplified file to work with. I'm still getting the type mismatch error even though it looks like everything should work! The code is under the Userform object.

Again, I would like Userform1.Listbox1 to auto select the items in Sheet1.Listbox1 upon opening Userform1.

Thanks in advance!!

Are you running the code without filling the listboxes first?

The listbox on the userform isn't filled when it's activated.

Try this.

Please Login or Register  to view this content.

Why are you saving the selected items in a listbox on a worksheet?

You could save them in a range on the worksheet, it would make it easier to reselect them when the form opens again.

Saving the values in another listbox seemed like the easiest way at the time! Ha. I have modified the workbook to your recommendation but I'm still one step from making it work properly. Here is the modified workbook (attached). The problem is that when the form opens, it runs the Listbox1_Change macro and deletes the saved values before they can be uploaded to the form. So nothing uploads. How do I get around that?

Thanks

Put the code to store the selected values in the UserForm's Terminate event.

Please Login or Register  to view this content.

Ooops. Can't delete this reply .