none
Accessing the selected item in a listbox RRS feed

  • Question

  • Hi All,

    I'm trying to insert a multiselect list box, which when item(s) are selected, filters a pivot table.

    However, I cannot seem to figure out how to programmatically determine the selected item in the listbox.

    Any/all help appreciated.

    Thanks!

    Thursday, September 24, 2015 6:40 PM

Answers

  • Hi Casey_M,

    Please use this code instead:

    Dim oo As Excel.OLEObject
    Dim ws As Worksheet
    Set ws = Worksheets("Firm")
    Dim i As Integer
    Set oo = ws.OLEObjects("lstFirms")
    With oo.Object
        For i = 1 To .ListCount
            If .Selected(i - 1) Then
               MsgBox """" & .List(i - 1) & """ was selected."
            End If
          Next i
    End With

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 25, 2015 7:11 AM
    Moderator

All replies

  • Code like this:

    Sub LBtest()
        Dim i As Integer
        
        With Worksheets("SheetName").ListBox1
            For i = 1 To .ListCount
                If .Selected(i - 1) Then
                    MsgBox """" & .List(i - 1) & """ was selected."
                End If
            Next i
        End With
    End Sub

    Thursday, September 24, 2015 7:12 PM
  • listbox.listindex return the index (0 to 1) of the selected item.

    https://msdn.microsoft.com/pt-br/library/office/ff193579.aspx

    Listbox(listbox.listindex, desiredColumnIndex).value returns the selected value

    • Proposed as answer by André Santo Monday, September 28, 2015 7:50 PM
    Thursday, September 24, 2015 7:13 PM
  • Thanks.

    I tried this, and modified the listbox and sheet names appropriately.

    However, when I run it, it tells me that the object doesn't support this method or property.  Error is on the WITH line...

    Sub lstFirms_Change()
    
        Dim i As Integer
            With Worksheets("Firm").lstFirms
            For i = 1 To .ListCount
                If .Selected(i - 1) Then
                    MsgBox """" & .List(i - 1) & """ was selected."
                End If
            Next i
        End With
    End Sub
    

    Thursday, September 24, 2015 8:00 PM
  • Put this into a standard codemodule... Which line errors then?

    Sub Test_lstFirms_Change() Dim i As Integer With Worksheets("Firm")

    With .lstFirms For i = 1 To .ListCount If .Selected(i - 1) Then MsgBox """" & .List(i - 1) & """ was selected." End If Next i

    End With End With End Sub

    Thursday, September 24, 2015 8:09 PM
  • Hi Casey_M,

    Please use this code instead:

    Dim oo As Excel.OLEObject
    Dim ws As Worksheet
    Set ws = Worksheets("Firm")
    Dim i As Integer
    Set oo = ws.OLEObjects("lstFirms")
    With oo.Object
        For i = 1 To .ListCount
            If .Selected(i - 1) Then
               MsgBox """" & .List(i - 1) & """ was selected."
            End If
          Next i
    End With

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 25, 2015 7:11 AM
    Moderator