Determine the selected item in a multiselect listbox

[Michael Simpson]

I have the following code in my change event for a listbox

Private Sub Customer_listbox_Change()

Dim counter As Integer

For counter = 1 To Customer_ListBox.ListCount Step 1

    If Me.Customer_ListBox.Selected(counter - 1) = True Then 'selected method has 0 base
        MsgBox ("Selected item")
    Else
      MsgBox ("DESelected item")

    End If
Next counter

End Sub

Now, obviously, all this does is to loop through the items in the listbox and indicate whether they've been selected or not.

What I would like to do is to be able to capture the entry in the listbox that started the change event.
Basically, I've populated the listbox with customers on a sheet. What I would like to do is to change the background colour for the sheet rows based on the items the user selects from the listbox. Assume there are 3 customers. If they select customer 1 from the listbox, then that customer row on the sheet will be coloured green. If they deselect customer 1, the colouring is removed.

I'm assuming my code needs to determine/get the customer's name from the item just (de)selected from the listbox so as to be able to determine which row on the sheet needs to be changed.

Any good suggestions appreciated.

 

[Andrew Poulsom]

Try:

Private Sub ListBox1_Change()
    Dim i As Long
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                Range(.RowSource).Cells(i + 1, 1).Interior.ColorIndex = 10
            Else
                Range(.RowSource).Cells(i + 1, 1).Interior.ColorIndex = xlNone
            End If
        Next i
    End With
End Sub

The code assumes you have used the RowSource property to populate the ListBox.

 

[MickG]

As you have Multiselect SET , the code below is a "Lost Focus" event so that it is based on All Selections , Not Changes.
Listfilled range assumed as Column "A".

Private [COLOR=navy]Sub[/COLOR] ListBox1_LostFocus()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Ray()
c = 0
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
Rng.EntireRow.Interior.ColorIndex = xlNone
[COLOR=navy]With[/COLOR] ListBox1
    [COLOR=navy]For[/COLOR] n = 0 To .ListCount - 1
        [COLOR=navy]If[/COLOR] .Selected(n) [COLOR=navy]Then[/COLOR]
            ReDim Preserve Ray(c)
            Ray(c) = .List(n)
            c = c + 1
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] With
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]For[/COLOR] n = 0 To UBound(Ray)
        [COLOR=navy]If[/COLOR] Dn = Ray(n) [COLOR=navy]Then[/COLOR]
            Dn.EntireRow.Interior.ColorIndex = 34
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Michael Simpson]

Found the answer here http://www.bigresource.com/Tracker/Track-vb-IbJf1ISJUP/

Basically, the code became

Private Sub Customer_listbox_Change()

Dim i As Integer
Dim selected_item As String

i = Me.Customer_ListBox.ListIndex
selected_item = Me.Customer_ListBox.List(i)
If Me.Customer_ListBox.Selected(i) = True Then 'selected method has 0 base
  MsgBox ("You selected " & selected_item)
Else
  MsgBox ("You DESelected " & selected_item)
End If

End Sub

 

[Andrew Poulsom]

I don't see any colouring of cells there.

 

[Michael Simpson]

Thank you Andrew

You are of course correct - there is no colouring there.

My main problem was "knowing"which item had been (de)selected. From there. it's a simple matter to select the relevant row and (de)colour it.

My problem was that I started trying to use

selected_item = Me.Customer_ListBox.[COLOR=Red]Value[/COLOR](i)

which kinda seemed logical to me. It wasn't until I found the use of .list in the reference I pointed to did I manage to get it working