+ Reply to Thread
Results 1 to 17 of 17

Auto select items in a Listbox with items from another Listbox

  1. #1
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    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?
    Last edited by perducci; 05-21-2013 at 06:09 PM.

  2. #2
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Auto select items in a Listbox with items from another Listbox

    To clarify one point:

    The Sheet's Listbox changes regularly. Sometimes there are more items in the list, sometimes less. Therefore, I need the Selected items in the Userform Listbox to change according to what the items in the Sheet's Listbox are at any given moment.

    Thanks!

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,460

    Re: Auto select items in a Listbox with items from another Listbox

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

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 05-21-2013 at 06:42 PM.

  4. #4
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Auto select items in a Listbox with items from another Listbox

    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

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,460

    Re: Auto select items in a Listbox with items from another Listbox

    Quote Originally Posted by perducci View Post
    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.
    Last edited by AlphaFrog; 05-21-2013 at 06:59 PM.

  6. #6
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Auto select items in a Listbox with items from another Listbox

    I must be crazy. It seems like this code is preventing the Type mismatch, which is good:

    Please Login or Register  to view this content.
    Yet the rest of the code is unresponsive. I've combed through it 10 times and it seems like it should work perfectly, but it's not!! What's wrong with me??

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,460

    Re: Auto select items in a Listbox with items from another Listbox

    Can you show the whole procedure?

  8. #8
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Auto select items in a Listbox with items from another Listbox

    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!!
    Attached Files Attached Files
    Last edited by perducci; 05-22-2013 at 12:18 PM.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,581

    Re: Auto select items in a Listbox with items from another Listbox

    Are you running the code without filling the listboxes first?
    If posting code please use code tags, see here.

  10. #10
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Auto select items in a Listbox with items from another Listbox

    No, the listboxes are always filled before activating the userform so there are always values in Userform1.Listbox1 however sometimes there are no values in Sheet1.Listbox1

    Make sense?

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,581

    Re: Auto select items in a Listbox with items from another Listbox

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

    Try this.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Auto select items in a Listbox with items from another Listbox

    Okay there is one more step I can't seem to solve to make this work. The purpose at the end of the day is to store Userform1.Listbox1 items in Sheet1.Listbox1 and have them automatically selected in Userform1.Listbox1 when the userform is activated (in other words, save the Userform selections within the workbook). I have a Listbox1_Change macro in order to adjust the items in Sheet1.Listbox1 as they are selected/deselected in Userform1.Listbox1. The following line of code (in bold) is preventing the userform from re-loading with the proper items selected:

    Please Login or Register  to view this content.
    Any ideas how to get around this last obstacle? See the attached updated version of the file
    Attached Files Attached Files

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,581

    Re: Auto select items in a Listbox with items from another Listbox

    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.

  14. #14
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Auto select items in a Listbox with items from another Listbox

    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
    Attached Files Attached Files
    Last edited by perducci; 05-22-2013 at 03:58 PM.

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,581

    Re: Auto select items in a Listbox with items from another Listbox

    Put the code to store the selected values in the UserForm's Terminate event.
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Auto select items in a Listbox with items from another Listbox

    Genius.

    Thank you Norie and also AlphaFrog for your minds.

  17. #17
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Auto select items in a Listbox with items from another Listbox

    Ooops. Can't delete this reply .
    Last edited by perducci; 05-22-2013 at 04:47 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1