VBA - Listbox select item, gives cell address

Question

Trying to get the cell address from a selected listbox item.

ListBox name: CBView

RowSource: F1:K99 (therefore, column K = column 5 in the listbox)

The objective is to select a line in the listbox and, with double-click, it gives me the address of one cell (the last column K). For example, double-click a line item and it gives me $K$33 as the cell address of that line item in column K. Where am I going wrong?

Private Sub CBView_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Dim rCell As Range
    Dim x As Long
    With CBView
        For x = CBView.ListCount - 1 To 1 Step -1
            If CBView.Selected(x) Then

               MsgBox CBView.List(.ListIndex, 5)
               MsgBox CBView.List(.ListIndex, 5).Address  ''''<<<<< Runtime error 424 object required.

            End If
        Next x
    End With

End Sub

Answer 1

I'm assuming you used the multicolumn list filling method? At least, I'm on excel 2007 and a form control list doesn't support multi columns.

In that case, you could say the list is populated 'one time' with values, rather than actual references to the range. This can be proven with the following statement:

MsgBox typename(CBView.List(.ListIndex, 5))

Outputting "string". A value is not an object - does not possess properties or methods - hence the error.

What you can do, however, is have a temp range furnish you with the address.

Const LROWST = 1
Const LCOLST = 6

'...rest of code...

msgbox cells(LROWST + .ListIndex,LCOLST + 5).address