Remove items from a listbox

[Andy]

I was wondering if anyone can help me, I have a listbox on a form in excel
that has row source of a data range from one of the sheets(called LIST). I
would like to add a button to the form that when clicked will remove the
currently selected item from the listbox and also removesthe corresponding
values from the worksheet.

Also, is it possible to take the removed values and paste them elsewhere on
the sheet?

Any help would be greatly appreciated.

 

[Bob Phillips]

Dim iRow As Long
With Me.ListBox1
On Error Resume Next
iRow = Application.Match(.Value, Range(.RowSource), 0)
On Error GoTo 0
If iRow > 0 Then
Range(.RowSource).Cells(iRow, 1).EntireRow.Delete
End If
End With


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Andy]

I appreciate the help, but the code you supplied is having problems as it
wasn't doing anything when the button was pressed. When I removed the on
error commands, it told me that the following line had a type mismatch error:

iRow = Application.Match(.Value, Range(.RowSource), 0)

 

[Bob Phillips]

That means it cannot find the data in the data.

What does the data look like, and where is it (cells) and what is your final
code?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Andy]

The data source for the list box is 5 columns on a spreadsheet (A:E), first
column contains numbers, second text, third numbers, fourth text and fifth
text.

The code i'm not too sure about as I was having problems getting it to
remove the item from the listbox and the corresponding data in the cells, so
the button the code is applied to is actually blank at the moment.

 

[Bob Phillips]

show us the code that is a problem.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Andy]

I haven't actually got any code to do the command at the moment as I couldn't
work out how to do it. Sorry if i'm not too clear, I just need the code to do
the following:

When an item is selected in a listbox and the button (that i'm applying the
code to) is pressed, it removes the item from the list box. After doing that
the code then needs to remove the corresponding value from the spreadsheet
(where I am getting my source for the listbox).

For example, I have a list set out in excel in the A column. This list is
the source for my list box. When you select a value from the list box and
press btn X, it removes the value from the list box and from the spreadsheet.

Thanks Andy