CODEWITHSUNDEEP
excelvba
FaruSZ
FaruSZ

Reputation: 61

listbox click vba excel

I have in an excel worksheet data arranged like this

enter image description here

I want to write a code to show the équipements (Column A to G) in a listbox and when I click on the equipement name I have the list of persons who know how to use the equipement to be displayed in another listox, the 2 listboxes are in the same userform.

I wrote this code but I didnt kow how to finish it : what should I write after additem ? 

Private Sub ListBox_Equip_Click()

    fin_liste_equip = ws_Liste_Equip.Cells(1, 256).End(xlToLeft).Column

    curVal = Me.ListBox_Equip.Value
    For x = 2 To fin_liste_equip
        If ws_Liste_Equip.Cells(x, fin_liste_equip) = curVal Then
            Me.ListBox_Pers_Mait.AddItem ws_Liste_Equip
        End

 If
Next x

you can see in the following picture that ive only known how to populate my 1st listbox enter image description here

Upvotes: 0

Views: 1061

Answers (2)

karma
karma

Reputation: 2009

Or maybe something like this ?

Private Sub ListBox_Equip_Click()
'fin_liste_equip = ws_Liste_Equip.Cells(1, 256).End(xlToLeft).Column
curVal = Me.ListBox_Equip.Value
Set c = Rows(1).Find(curVal, lookat:=xlWhole)
If c.Offset(1, 0).Value = "" Then Exit Sub

Set Rng = Range(c.Offset(1, 0), c.End(xlDown))
ListBox_Pers_Mait.Clear
If Rng.Rows.Count = 1 Then ListBox_Pers_Mait.AddItem c.Offset(1, 0).Value
If Rng.Rows.Count > 1 Then ListBox_Pers_Mait.List = Application.Transpose(Rng)

'For x = 2 To fin_liste_equip
'    If ws_Liste_Equip.Cells(x, fin_liste_equip) = curVal Then
'        Me.ListBox_Pers_Mait.AddItem ws_Liste_Equip
'    End
End Sub

The code assumes that there won't be a duplicate équipements name across the column of row 1. Or in other words, your ListBox_Equip item is unique.

Upvotes: 1

Tom
Tom

Reputation: 9878

First you'll need to set the number of columns you want to use in your ListBox using (put this outside your loop)

Me.ListBox_Pers_Mait.ColumnCount = 7

Then modify your If statement to use something like the following

With Me.ListBox_Pers_Mait    
    .AddItem ws_List_Equip
    .List(.ListCount - 1, 1) = "Your Next Value"
    .List(.ListCount - 1, 2) = "Another Value"
    ' ......etc.
End With

We subtract 1 off of the .ListCount as the index of the ListBox starts at 0.

Upvotes: 0

Related Questions