+ Reply to Thread
Results 1 to 8 of 8

Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox entry.

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    37

    Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox entry.

    Hi,

    I have an excel userform which submits data via a command button and puts it on the next row of the spreadsheet. This is already coded and works fine, however: What I have within the userform is a primary listbox (named ListBox3) containing countries from which I need to select a few and add them to a secondary listbox (ListBox4) - and thus removing them from the primary (to avoid duplication in the listboxes). With this, there is a tricky part... I want to be able to submit the SAME data entered into the userform, but submitting it on a seperate row for each country selected. So for example:

    ListBox3:
    Albania
    <item selected and moved to ListBox4>
    Belgium
    <item selected and moved to ListBox4>
    South Africa
    <item selected and moved to ListBox4>
    Tunisia
    <item selected and moved to ListBox4>
    Zanzibar
    <item selected and moved to ListBox4>

    ListBox4:
    (assume that Algeria, Belarus, Spain, Turkey and Zimbabwe were in ListBox3 and I selected them, moved it to ListBox 4 - thus removing it from Listbox3)


    THEN (after submitting all data to spreadsheet, data should look like this):

    Job Description Number Country
    1 Telephone 09876 Algeria
    1 Telephone 09876 Belarus
    1 Telephone 09876 Spain
    1 Telephone 09876 Turkey
    1 Telephone 09876 Zimbabwe

    Noting the same data for each row except for the country.

    Can anyone help me with this, it's been driving me crazy! Thanks in advance!!

    Jamie

  2. #2
    Registered User
    Join Date
    08-30-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox ent

    Bump.

    Does anyone have any ideas please? I need to try and get this project finished within the next 2 days. Thanks!

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

    Re: Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox ent

    Jamie

    This should be straightforward especially if all you are doing is repeating the same data.

    Could you attach a sample workbook so we can see what you have so far?
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    08-30-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox ent

    Hi Norie, Thanks for your reply - I have attached a sample of what I want (with the details in GREEN CAPS within the VBA code). I hope this is helpful. Thanks in advance for any help you can give me.
    Attached Files Attached Files

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

    Re: Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox ent

    Take a look at the attached.

    Note, I had to change how the primary listbox was populated - if you use RowSource VBA doesn't like you trying to delete items.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-30-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox ent

    That is just perfect, exactly what I wanted it to do - that's going to save me heaps of time thanks to you! What a star, thanks Norie! One question I would like to add to this if possible please if you could answer (regarding this), is there any way I can Alphabetize the lists each time it is added/removed please?

    Thanks again!

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

    Re: Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox ent

    I knew you would ask that.

    I'll have a look at it.

  8. #8
    Registered User
    Join Date
    08-30-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox ent

    Haha, thank you! :D

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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