I have a Listbox fed by RowSource delivering data from Cols A-C. The code below
deletes a selected row from both the ListBox and the root row on the worksheet. It works fine except that, try as I would, it does the job uglily. For one, I have violated one of the cardinal principles of good programming by pandering to the use of GO TO in a way which makes the code poorly structured.
Could someone kindly have a quick study and restructure the logical flow without having to loop backwards the way I did? Many thanks.
[PS: I would also love the code to allow for multiple row selection and resultant block deletions, if possible].
David.
Private Sub CmdDelete_Click()
Restart:
If ListBox1.ListIndex = -1 Then 'no selection
ans = MsgBox("Select item to delete", vbYesNo + vbDefaultButton2)
If ans = vbYes Then
ListBox1.Selected(0) = True 'select 1st item for a start
GoTo Skip
Else
ListBox1.ListIndex = -1
Exit Sub
End If
End If
Skip:
If ListBox1.Selected(1) =False True Then
If ListBox1.Selected(ListBox1.ListIndex) = True Then
ansx = MsgBox("Do you wish to delete selection?" & vbCrLf & " " & ListBox1.List(ListBox1.ListIndex, 0), vbYesNo + vbDefaultButton2 + vbInformation)
If ansx = vbNo Then Exit Sub
ActiveSheet.Cells(ListBox1.ListIndex + 1, 1).Resize(, 3).ClearContents
On Error Resume Next
ListBox1.Selected(ListBox1.ListIndex) = False
ansx = MsgBox("Do you wish to delete another?", vbYesNo + vbDefaultButton1 + vbInformation)
If ansx = vbNo Then
GoTo Sortt
Else
GoTo Restart
End If
End If
End If
Sortt:
Columns("a:c").Sort Key1:=Range("A2"), Key2:=Range("b2"), Key3:=Range("c2"), Header:=xlNo
ListBox1.RowSource = "a1:c" & [a65536].End(xlUp).Row
End Sub
Bookmarks