Listbox multiselect (Excel 2003)

Topic

New Reply

Hi all,

I’m trying to extract the values of a multiselect listbox and place the selected items into cells while offsetting each item in the list, as it is with what I have, I can only extract the last value.

Thanks Darryl.

Private Sub CommandButton1_Click()

Dim i As Long
Dim Msg As String
With Me.ListBox1
For i = 0 To .ListCount – 1
If .Selected(i) Then
ActiveCell.Offset(1, 0).Value = .List(i)
End If
Next i
End With
End Sub

Reply | Quote

Replies

You write each value to the same cell: the cell below the active cell. One solution is to move the active cell:

For i = 0 To .ListCount – 1
If .Selected(i) Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = .List(i)
End If
Next i

It’s better not to change the selection:

Dim j As Long
For i = 0 To .ListCount – 1
If .Selected(i) Then
j = j + 1
ActiveCell.Offset(j, 0).Value = .List(i)
End If
Next i

Reply | Quote

Thanks Hans.

I wanted to ask you a question regarding this example, in the original example I didn’t have a qualifier, but here I added it. My question is , how come I didn’t have to set the object?

Thanks Darryl.

Dim j As Long
with listbox1
For i = 0 To .ListCount – 1
If .Selected(i) Then
j = j + 1
ActiveCell.Offset(j, 0).Value = .List(i)
End If
Next i
end with

Reply | Quote

Which object do you mean, and what qualifier?

Reply | Quote

Hans,

when I tried to run this snippet I recieved an error, invalid qualifer, the help documentation said that I needed to use the expression in a with, end with block. becasue I had used the dot notation. (.list). I thought the object was listbox1, I know I can be wrong.

Thanks, Darryl.

Reply | Quote

The code itself is OK, it runs without error when I try it in a test workbook. So there must be a problem somewhere else. If you wish, you can post (a stripped down copy of) your workbook.

Reply | Quote

Hans,

here you go, the code as it is now produces the error invaild reference, how come I don’t have to set the listbox1?

Thanks Darryl.

Reply | Quote

You have to use a with – end with since you have unqualified references. (You have a “dot” rpeceding an object with no reference to what it refers to)

You do not in any of your other code.

Private Sub CommandButton1_Click()
Dim j As Long
With ListBox1
For i = 0 To .ListCount - 1
    If .Selected(i) Then
        j = j + 1
        ActiveCell.Offset(j, 0).Value = .List(i)
    End If
Next i
End With
End Sub

Reply | Quote

Thanks Steve,

I understand that part, about the invaild reference, what I don’t understand is the object, how come I do not have to “set” this like I would the a range object?

Thanks,
Darryl.

Reply | Quote

You don’t use an object variable. You can use the following:

1) A direct reference:

ListBox1.Visible = True

2) Use an object variable:

Dim lb As Forms.ListBox
Set lb = Listbox1
lb.Visible = True

3) Use With … End With:

With ListBox1
.Visible = True
End With

Reply | Quote