Listbox 2 takes the value of Listbox 1

[Illya Teideman]

Hi, I have an excel sheet with two listboxes controlled by forms. The
listboxes are populated from different data and when clicked populate
information in Sheet1. Listbox1 has 20 rows of data, Listbox2 has 10 rows.
When the first listbox pops up I can select any of the entries. If I select
row 2 the data is populated in the correct place in sheet1. The second
listbox doesn't show up but the data from row 2 of the second list box is
automatically populated in the correct place in sheet1. If I run this again
and pick row 11 in listbox1 the second list box shows up and everything works
correctly. The second listbox appears to be picking up the value from the
first listbox and only shows up if that number doesn't correspond to
anything. Any ideas on how to fix that?

 

[challa prabhu]

Hi,

Excel -- Data Validation -- Create Dependent Lists.

Use this URL: http://www.contextures.com/xlDataVal02.html

Challa Prabhu

 

[Illya Teideman]

Thanks for the thought, However I'm using listboxes from forms.
Dropdown lists are unfortunately not suitable for what I am doing.

 

[Illya Teideman]

I have found a workaround. By putting a message box at the end of listbox2
everything works correctly. The extra click seems to stop the next listbox
from running automatically. I still have no idea why this is happening so any
further ideas would be greatfully received. (At least I have a quick and
dirty workaround for now)

--Listbox1
Private Sub ListBox1_Click()
ActiveSheet.Range("B2") = ListBox1.List(ListBox1.Value, 0) & " " &
ListBox1.List(ListBox1.Value, 1)
MsgBox "test"
Unload Me
End Sub

--Listbox2
Private Sub ListBox2_Click()
ActiveSheet.Range("B3") = ListBox1.List(ListBox1.Value, 0) & " " &
ListBox1.List(ListBox1.Value, 1)
Unload Me
End Sub