Selected ListBox items to worksheet
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Selected ListBox items to worksheet

  1. #1
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Selected ListBox items to worksheet

    I am using the following to establish which ListBox items have been selected
    Code:
    Private Sub btnSend_Click()
    
    Dim rngBuyer As Range
    
    Sheets("Holding").Range("Holding_SendClear").ClearContents
    
    Set rngBuyer = Sheets("Holding").Range("Holding_PortfolioCatalogueSendStart").Offset(1, 0)
    
    For intItem = 0 To lbBuyers.ListCount - 1
       If lbBuyers.Selected(intItem) = True Then
          rngBuyer = lbBuyers.List(intItem)
          
          Set rngBuyer = rngBuyer.Offset(1, 0)
          Else
       End If
    Next intItem
    
    Set rngBuyer = Nothing
    
    End Sub
    At the 1st line to ClearContents of any exiting list in the holding worksheet, the selected items in the ListBox are cleared/deselected and the loop doesn't detect any selected items.

    Not entirely sure why and I can;t seem to find any answers anywhere. Can someone shed some light on why this is happening and how to resolve?

    TIA

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,685
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Selected ListBox items to worksheet

    Not sure what your wanting to do.
    Here is a script of mine that loops through selected values in a Multiselect listbox.
    Code:
    Private Sub CommandButton2_Click()
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "B").End(xlUp).Row + 1
    Dim i As Long
        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) Then
                Cells(Lastrow, 2).Value = ListBox1.List(i)
                Lastrow = Lastrow + 1
                ListBox1.Selected(i) = False
            End If
        Next
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selected ListBox items to worksheet

    I am doing pretty much the exact same thing
    Code:
    Set rngSend = Sheets("Holding").Range("Holding_5StartlbBuyers").Offset(5000, 0).End(xlUp).Offset(1, 0)
    
    For iCtr = 0 To lbBuyers.ListCount - 1
       If lbBuyers.Selected(iCtr) = True Then
          rngSend = lbBuyers.List(iCtr)
          
          Set rngSend = rngSend.Offset(1, 0)
          
          Else
       End If
    Next iCtr
    At the point of
    Code:
    rngSend = lbBuyers.List(iCtr)
    executing, all the selected items in the lbBuyers listbox are deselected.

    The listbox is set as a multi-select listbox.
    Last edited by mikeymay; Mar 19th, 2018 at 09:35 AM.

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,211
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Selected ListBox items to worksheet

    Did you bind the listbox directly to a range? If so, that's the problem.

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,357
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selected ListBox items to worksheet

    How have you populated the listbox?
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selected ListBox items to worksheet

    Yes I am using a dynamic range in the RowSource as I need to keep the list in alphabetical order.

    Basically I have 2 listboxes
    1 Buyers - Populated with D_Buyers dynamic range
    2 ToEmail - Populated with D_ToEmail dynamic range

    When items are selected in buyers and the 'Add' button is clicked, I need the selected items to move from the Buyers listbox to the ToEmail listbox but for the ToEmail listbox to be in alphabetical order.

    I have a 'Remove' button as well so any items selected in the Buyers listbox and added to the ToEmail listbox, can be moved back and again the BUyers listbox needs to be in alphabetical order all the time.

    Is there a way of doing this without having to rebuild each list by using AddItem every time items are moved between the lists?


    Thanks

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,211
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Selected ListBox items to worksheet

    Do you actually need to use RowSource rather than just assigning the values using the List property?

  8. #8
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selected ListBox items to worksheet

    I don;t have to use a RowSource source but I do need to keep each list in alphabetical order as each one changes with Add/Remove.

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,211
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Selected ListBox items to worksheet

    If you switch from Rowsource to List, you can still use a range if necessary.

  10. #10
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selected ListBox items to worksheet

    ††
    How do I do that?

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com