Listboxes with filtered data > import data from table to userform?

[behedwin]

Hi

Please view my example file here

file is attached to this post.
18607

I have one userform with 2 listboxes.

I populate the listboxes with data from one table based on autofilter.
In one listbox data is based on GREEN value and other RED value from the table.

When i select a value in the either listbox i want that row to import to textboxes in the userform.

I can not get this to work in my example file.
Any help would be great.

[rlv]

This looks like a homework assignment so I'll give you a hint rather than a solution: you are attempting to use each listbox's ListIndex value as the basis for getting the data from the table. Notice that your example code works ok for listbox #3. That's because the contents of listbox #3 matches the contents of the table, so it works. For listboxes #1 & #2 that is not the case and so it does not work. Think about creating some code that builds an accurate index into the table based on the selected item in listbox #1 or #2.

[behedwin]

This looks like a homework assignment so I'll give you a hint rather than a solution: you are attempting to use each listbox's ListIndex value as the basis for getting the data from the table. Notice that your example code works ok for listbox #3. That's because the contents of listbox #3 matches the contents of the table, so it works. For listboxes #1 & #2 that is not the case and so it does not work. Think about creating some code that builds an accurate index into the table based on the selected item in listbox #1 or #2.

Thanks for your reply.
This is not any homework.
I am building this for my work. I dont know how to solve this based on your tips.
But i understand what is wrong.

[rlv]

Here's a simple example of what I mean by building a better index


Sub fetch_data_listbox1()


Dim fetchrow As Integer

For fetchrow = 1 To UserForm1.ListBox3.ListCount
If UserForm1.ListBox3.List(fetchrow - 1) = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex) Then
Exit For
End If
Next fetchrow


fetchrow = 2 + fetchrow


UserForm1.TextBox1 = Format(Sheets("Register").Range("a" & fetchrow), "")
UserForm1.TextBox2 = Format(Sheets("Register").Range("b" & fetchrow), "")
UserForm1.TextBox3 = Format(Sheets("Register").Range("c" & fetchrow), "")
UserForm1.TextBox4 = Format(Sheets("Register").Range("d" & fetchrow), "")
End Sub

[behedwin]

Here's a simple example of what I mean by building a better index


Sub fetch_data_listbox1()


Dim fetchrow As Integer

For fetchrow = 1 To UserForm1.ListBox3.ListCount
If UserForm1.ListBox3.List(fetchrow - 1) = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex) Then
Exit For
End If
Next fetchrow


fetchrow = 2 + fetchrow


UserForm1.TextBox1 = Format(Sheets("Register").Range("a" & fetchrow), "")
UserForm1.TextBox2 = Format(Sheets("Register").Range("b" & fetchrow), "")
UserForm1.TextBox3 = Format(Sheets("Register").Range("c" & fetchrow), "")
UserForm1.TextBox4 = Format(Sheets("Register").Range("d" & fetchrow), "")
End Sub


Thank you, that worked really good.