ActiveX Listbox control Index

[Len Piwowar]

Is there a way to return an interger representing the selected value row in a ActiveX embedded listbox?

I know it can be done using a forms listbox but, the activeX embedded Listbox works differently when selecting a value in a listbox Listbox.index does not return an indexed interger. The Listbox selected value returns only the selected .text / .value and not a Indexed number.

I embedded a Listbox from the contols toolbar because I wanted the Listbox to allways stay in the same position on the sheet without the overhead of writting code to position it. Values are loaded from the worksheet depending on which cell is selected. The fill values change by changing the range name for ListFillRange = RpsListBxSettings(1). I have a need to return a a Indexed Interger value.

Sub SetRpsListBox(RpsListBxSettings)
Application.ScreenUpdating = True
Application.ActiveWorkbook.Worksheets("Rps").Activate
Application.ActiveWorkbook.Worksheets("Rps").RpsListBox.ListFillRange = RpsListBxSettings(1)
Application.ActiveWorkbook.Worksheets("Rps").RpsListBox.ColumnCount = RpsListBxSettings(2)
Application.ActiveWorkbook.Worksheets("Rps").RpsListBox.TextColumn = RpsListBxSettings(3)
Application.ActiveWorkbook.Worksheets("Rps").RpsListBox.ColumnWidths = RpsListBxSettings(4)
End Sub

[Zack Barresse]

Hi Len,

Not sure how you want to accomplish this, but this may help ...

Option Explicit

Private Sub CommandButton1_Click()
    Dim i As Integer, strItem As String, lngItem As Long
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            strItem = ListBox1.List(i)
            lngItem = i + 1
        End If
    Next i
    MsgBox "Item " & strItem & ", is in position " & lngItem & "."
    End
End Sub

I'm sure there's an easier way other than looping through the whole listbox, but it works.

[Andy Pope]

Hi,

You can use the ListIndex property,

Private Sub CommandButton1_Click()
    With ActiveSheet.ListBox1
        MsgBox "Item " & .Value & " Index=" & .ListIndex
    End With
End Sub

As you appear to be filling the listbox from a range of cells you could use the LinkedCell property, which would return the selected items text, in conjunction with the MATCH() function.

[Len Piwowar]

.ListIndex was what I was looking for. I had searched thru the help files and never came across .ListIndex. I'm self taught and I've purchased books and software and still have a hard time finding the proper code to do certain task. I typed in Index in both the VBA editor and browser and never came up with .List____.

Thanks Andy & firefytrfor the help!