Link to home
Start Free TrialLog in
Avatar of Golfer219

asked on

VBA UserForm - Remove values from Listbox

I have a listbox (ListBox1) on a user form of 'selected customers' which shows data from 8 columns and is linked to a named range on a sheet named 'Selected Customers'.  ListBox1 also is a fmMultiSelectMulti so more than on item may be selected at a time.

I'm looking for code (most likely a loop) that will take each value that's selected in ListBox1 and will remove it from the sheet 'Selected Customers' (note - it needs to delete data in columns A through S, however it cannot delete the whole row - it can only delete those cells and move the cells up).

Thanks for your help!!
Avatar of Norie

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Golfer219


Thanks imnorie - I added your code and it works very good except for one small bug.  When I select the second result in ListBox1 to remove, it actually removes the first one instead.  It looks like it's removing one prior to the one being selected.

Any thoughts?  Thanks for your help!!
The code works fine for me,  when I select the 2nd item that's the one removed from the listbox and from the worksheet.

What range is your data in?

How did you populate the listbox?

Does it do this with any of the other items?
Just thought of one thing - does your data on the 'Selected Customers' tab have column headers in it?  I think that may be the one difference in our data sets.

The data range that is populated in the list box does not have the column headings in it, but they are in row 1 of the other tab.
The sheet, and the range, I've used don't have headers.

I did try it with headers when I saw your response and the code still worked.

What is your range and how did you populate the listbox?

Also, where did you put the code?
Here's the code that I've used.  It's contained within a sub and called when a button on a userform is clicked.

Private Sub CommandButton4_Click()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim I As Long
     For I = ListBox1.ListCount - 1 To 0 Step -1
            If ListBox1.Selected(I) = True Then
                 Sheets("Selected Customers").Rows(I + 1).Delete xlShiftUp
            End If
     Next I

UserForm3.ListBox1.RowSource = "blankrowsource"
UserForm3.ListBox1.RowSource = "selected_customers"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

BlankRowSource is linked to a blank range of cells to clear our the listbox while selected_customers is a dynamic range based on the customers that appear on the 'Selected Customers' tab (range includes data through A2 through the last row of data in column S).

This is the workbook I worked with.

Rememember it's dummy data, try and ignore all but the first column.

I've used a different method to populate the listbox.

PS If you want to clear a listbox using RowSource set it to "".
Thanks for all your help!  I went back to the original code you sent and changed my dynamic range to start with the header row and offset it one row and everything worked great.  Thanks for your help1!
Glad it worked.

Thought there might be something up with the range and the way it was being defined.