Load Userform Listbox list from worksheet cell

[Celticfc]

Hi,

A1 has a list of client names with debts; B1 has a list of all clients in our business. Names are separated by commas.

I have a userform with 2 listboxes. Listbox1 should show the list of clients with debts;
Listbox2 should lists the clients without debts.

Here’s an example:

A1: Client1, Client 3, Client 5,

B1: Client 1, Client 2, Client 3, Client 4, Client 5

Therefore, Listbox1 should list everything in A1, Listbox2 should only list Client 2 and Client 4.

Would really appreciate support, thank you.

 

[MickG]

Try this in your Userform Code Module:-
NB:- Your 2 set of data do not match (Two places) where they should ("Client 1, and Client1" and "Client 5, and Client 5"")

Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
[COLOR=navy]Dim[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant, Sp1 [COLOR=navy]As[/COLOR] Variant, Ray [COLOR=navy]As[/COLOR] Variant, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
 Sp = Split([B1], ", ")
    Sp1 = Split([a1], ", ")
        [COLOR=navy]For[/COLOR] n = 0 To UBound(Sp1)
            Ray = (Filter(Sp, Sp1(n), False))
            Sp = Ray
        [COLOR=navy]Next[/COLOR] n

ListBox1.List = Sp1
ListBox2.List = Ray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Celticfc]

Try this in your Userform Code Module:-
NB:- Your 2 set of data do not match (Two places) where they should ("Client 1, and Client1" and "Client 5, and Client 5"")

Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
[COLOR=navy]Dim[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant, Sp1 [COLOR=navy]As[/COLOR] Variant, Ray [COLOR=navy]As[/COLOR] Variant, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
 Sp = Split([B1], ", ")
    Sp1 = Split([a1], ", ")
        [COLOR=navy]For[/COLOR] n = 0 To UBound(Sp1)
            Ray = (Filter(Sp, Sp1(n), False))
            Sp = Ray
        [COLOR=navy]Next[/COLOR] n

ListBox1.List = Sp1
ListBox2.List = Ray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Hi, this works great however when listbox1 or 2 are empty(as I transfer contents between them, one of them can become empty), I amgetting an error when sending listbox contents to C1: Error 381.
How can I add a condition that if either list boxes areempty, it should just paste blank in C1 etc.
Thanks again.

 

[MickG]

What is your code for Transferring data to "C1".???