Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Listbox multiselect (Excel 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listbox multiselect (Excel 2003)

    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

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Listbox multiselect (Excel 2003)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listbox multiselect (Excel 2003)

    Which object do you mean, and what qualifier?

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Listbox multiselect (Excel 2003)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listbox multiselect (Excel 2003)

    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.

  7. #7
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Listbox multiselect (Excel 2003)

    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.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Listbox multiselect (Excel 2003)

    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.

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


  9. #9
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Listbox multiselect (Excel 2003)

    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.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listbox multiselect (Excel 2003)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •