listbox

[jim c.]

i have a listbox on a userform whose rowsource is a named
range on sheet1. im trying the following code to delete
selected rows from named range, but will only delete first
selected item in list.
****************************************************

Private Sub CommandButton1_Click()

Dim x As Long

For x = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(x) = True Then
Sheets("sheet1").Rows(x + 1).Delete
End If
Next x

End Sub
****************************************************

how would i go about deleting all the selected rows???

 

[Lance]

try changing the line
For x = 0 To ListBox1.ListCount - 1
to
For x = ListBox1.ListCount - 1 to 0 step -1

note: make a backup first

Lance

 

[jim c.]

thanks for the help Lance, but now it only deletes the
first selected row in reverse order. Jim Carlock is
right. I'm losing focus in the listbox. Cant seem to find
a way to stop it. Thanks for your help guys...

 

[Peter Beach]

Hi Jim,

If the contents of the ListBox are bound to the sheet you are going to have
a problem I think. When you delete the row from the sheet, you also delete
the row from the listbox and I would imagine that XL becomes a little
confused.

The following code appears to do what you want:

Private Sub CommandButton1_Click()
Dim bDelete() As Boolean
Dim i As Long
Dim nDeletes As Long

ReDim bDelete(ListBox1.ListCount - 1) As Boolean

For i = 0 To ListBox1.ListCount - 1
bDelete(i) = ListBox1.Selected(i)
Next i

nDeletes = 0
For i = 0 To UBound(bDelete)
If bDelete(i) Then
ThisWorkbook.Worksheets(1).Rows(i + 1 - nDeletes).Delete
nDeletes = nDeletes + 1
End If
Next i
End Sub

It has the effect of deleting the selected items from both the list box and
the worksheet. I presume that is what you were after?

HTH

Peter Beach

 

[jim c.]

thanks Peter... it works perfect...

-----Original Message-----
Hi Jim,

If the contents of the ListBox are bound to the sheet you are going to have
a problem I think. When you delete the row from the sheet, you also delete
the row from the listbox and I would imagine that XL becomes a little
confused.

The following code appears to do what you want:

Private Sub CommandButton1_Click()
Dim bDelete() As Boolean
Dim i As Long
Dim nDeletes As Long

ReDim bDelete(ListBox1.ListCount - 1) As Boolean

For i = 0 To ListBox1.ListCount - 1
bDelete(i) = ListBox1.Selected(i)
Next i

nDeletes = 0
For i = 0 To UBound(bDelete)
If bDelete(i) Then
ThisWorkbook.Worksheets(1).Rows(i + 1 - nDeletes).Delete
nDeletes = nDeletes + 1
End If
Next i
End Sub

It has the effect of deleting the selected items from both the list box and
the worksheet. I presume that is what you were after?

HTH

Peter Beach





.