hello,

i am new to this site but i need some help. i have a userform with a
listbox on it. the list box has 20 columns and upto 200 rows.

i populate the list box via the row source property.

i want to click on a row within the list and delete it. i can do this
as i find the row i am on by setting a variable to the list control
source which i have set elseware on another worksheet.

i have this code assigned to a command button on the form

sub deleterow ()
dim rgstack as range
clears = worksheets("sheet2").Range("a1")
Set rgstack = worksheets("sheet1").Range("a1:a2000").Find(what:= clears)
rgstack.EntireRow.Delete Shift:=xlUp
end sub


now here is the real problem, this code works fine on xl2000 but on
xl97 it works once then the second time the row does not disapear.

is it a code compatibility issue or is there a better way of updating a
list when deleting a row

regards
tammy

I don't have xl97 to test it on, but maybe you could use a different technique.

Just delete the row that's selected and then refresh the listbox.

Option Explicit
Dim myRowSourceRange As Range
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
With Me.ListBox1
If .ListIndex -1 Then
'something is selected
myRowSourceRange.Cells(1, 1).Offset(.ListIndex).EntireRow.Delete
End If
End With
Call UserForm_Initialize
End Sub
Private Sub UserForm_Initialize()
With Worksheets("sheet1")
Set myRowSourceRange = .Range("a1:H" & .Cells(.Rows.Count,
"A").End(xlUp).Row)
With Me.ListBox1
.ColumnCount = 8 'for testing
.RowSource = myRowSourceRange.Address(external:=True)
End With
End With
End Sub



wrote:

hello,

i am new to this site but i need some help. i have a userform with a
listbox on it. the list box has 20 columns and upto 200 rows.

i populate the list box via the row source property.

i want to click on a row within the list and delete it. i can do this
as i find the row i am on by setting a variable to the list control
source which i have set elseware on another worksheet.

i have this code assigned to a command button on the form

sub deleterow ()
dim rgstack as range
clears = worksheets("sheet2").Range("a1")
Set rgstack = worksheets("sheet1").Range("a1:a2000").Find(what:= clears)
rgstack.EntireRow.Delete Shift:=xlUp
end sub

now here is the real problem, this code works fine on xl2000 but on
xl97 it works once then the second time the row does not disapear.

is it a code compatibility issue or is there a better way of updating a
list when deleting a row

regards
tammy

--

Dave Peterson