Records deleted from listbox

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi guys,

I am using this code to transfer records from a multiselect listbox1 to a specific sheet, I was wondering if there is any way every time I transfer records from the listbox using that command button1, the records are deleted from the listbox, thus from the sheet they came from.

Private Sub CommandButton1_Click()
Dim sTgtSht As String
Dim rngLastCell As Range
Dim lLastRow As Long
Dim sActiveSheet As String

sActiveSheet = ActiveSheet.Name

sTgtSht = ComboBox1.Value

If (sTgtSht = "") Then
MsgBox "No target sheet select."
GoTo End_Sub
End If

On Error Resume Next
Sheets(sTgtSht).Select
Err.Clear
On Error GoTo 0

If ActiveSheet.Name <> Sheets(sTgtSht).Name Then
MsgBox "Sheet '" & sTgtSht & "' not found."
GoTo End_Sub
End If

Set rngLastCell = Sheets(sTgtSht).Cells.Find("*", Cells(1, 1), , , xlByRows, xlPrevious)

If rngLastCell Is Nothing Then
lLastRow = 1
Else
lLastRow = rngLastCell.Row
End If

Set rngLastCell = Nothing

For iListCount = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(iListCount) = True Then

lLastRow = lLastRow + 1
ListBox1.Selected(iListCount) = False
Sheets(sTgtSht).Cells(lLastRow, "A") = ListBox1.List(iListCount, 0)
Sheets(sTgtSht).Cells(lLastRow, "B") = ListBox1.List(iListCount, 1)

End If

Next iListCount

End_Sub:
Sheets(sActiveSheet).Select

End Sub


Thanks in advance for your help

1 reply

sorry I posted twice the same problem.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You mean thrice