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!!

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Golfer219

ASKER

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!!

Norie

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?

Golfer219

ASKER

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.

Norie

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?

Golfer219

ASKER

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).

Thanks

Norie

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 "".
EE---03Jul2012---DeleteFromListb.xlsm

Golfer219

ASKER

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!

Norie

Glad it worked.

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