Selected ListBox items to worksheet

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,598
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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:
Upvote 0
Did you bind the listbox directly to a range? If so, that's the problem.
 
Upvote 0
How have you populated the listbox?
 
Upvote 0
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
 
Upvote 0
Do you actually need to use RowSource rather than just assigning the values using the List property?
 
Upvote 0
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.
 
Upvote 0
If you switch from Rowsource to List, you can still use a range if necessary.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top