Search and delete

[LegenDSlayeR]

Good Evening,

I have create a Userform for Warehouse Air Stock. i have multiple products stored in Air Locations. as you will see in a screenshot. i have 2 listboxes. one that show the airstock worksheet, and the other listbox is a search.

my question is, Is it possible to search A product code, and delete it the row selected using the delete button. i have the delete button working BUT. when i search for the product code it gives me 8 matching results. when i click the delete button it removes one of the TOP LINES 8 in the worksheet. and not the on highlighted in the search list box.




Can anyone one help.

sorry if it doesnt make sense

These are the lines of code using for both buttons.



Private Sub CommandButton1_Click()
    Dim RowNum As Long
        RowNum = 2
            Do Until Sheets("airstock").Cells(RowNum, 1).Value = ""
          
                If InStr(1, Sheets("airstock").Cells(RowNum, 1).Value, txtsearch.Value, vbTextCompare) > 0 Then
                    On Erro GoTo next1
                    lstsearch.AddItem Sheets("airstock").Cells(RowNum, 1).Value
                    lstsearch.List(lstsearch.ListCount - 1, 1) = Sheets("airstock").Cells(RowNum, 2).Value
                    lstsearch.List(lstsearch.ListCount - 1, 2) = Sheets("airstock").Cells(RowNum, 3).Value
                    lstsearch.List(lstsearch.ListCount - 1, 3) = Sheets("airstock").Cells(RowNum, 4).Value
                    lstsearch.List(lstsearch.ListCount - 1, 4) = Sheets("airstock").Cells(RowNum, 5).Text
                    lstsearch.List(lstsearch.ListCount - 1, 5) = Sheets("airstock").Cells(RowNum, 6).Value
                    lstsearch.List(lstsearch.ListCount - 1, 6) = Sheets("airstock").Cells(RowNum, 7).Value
                    lstsearch.List(lstsearch.ListCount - 1, 7) = Sheets("airstock").Cells(RowNum, 8).Value
End If

next1:
    RowNum = RowNum + 1
    Loop
    lstsearch.RowSource = ""
  
End Sub

Private Sub CommandButton2_Click()
    Dim i As Integer
        For i = 0 To Range("A10209").End(xlUp).Row - 1
        If lstsearch.Selected(i) Then
            Rows(i).Select
            Selection.Delete
        End If
    Next i

  End Sub

Reguards

Jamie.

 

[dmt32]

Hi,
Welcome to forum

When producing a filtered list in a listbox, you need to retain reference to the row the record was taken from if you want to perform an action like deleting the selected record from the range. You can do this by storing the row value in the last column of the listbox

Try these updated codes in your project & see if will do what you want

Dim wsAirStock As Worksheet

Private Sub CommandButton1_Click()
    Dim RowNum      As Long
  
  
    RowNum = 2
  
    With lstsearch
        .RowSource = ""
        .Clear
        .ColumnCount = 9
      
        Do Until wsAirStock.Cells(RowNum, 1).Value = ""
          
            If InStr(1, wsAirStock.Cells(RowNum, 1).Value, Me.txtsearch.Value, vbTextCompare) > 0 Then
              
                .AddItem wsAirStock.Cells(RowNum, 1).Value
                .List(.ListCount - 1, 1) = wsAirStock.Cells(RowNum, 2).Value
                .List(.ListCount - 1, 2) = wsAirStock.Cells(RowNum, 3).Value
                .List(.ListCount - 1, 3) = wsAirStock.Cells(RowNum, 4).Value
                .List(.ListCount - 1, 4) = wsAirStock.Cells(RowNum, 5).Text
                .List(.ListCount - 1, 5) = wsAirStock.Cells(RowNum, 6).Value
                .List(.ListCount - 1, 6) = wsAirStock.Cells(RowNum, 7).Value
                .List(.ListCount - 1, 7) = wsAirStock.Cells(RowNum, 8).Value
                .List(.ListCount - 1, 8) = RowNum
            End If
          
            RowNum = RowNum + 1
        Loop
    End With
End Sub

Private Sub CommandButton2_Click()
   'declare the variables
    Dim Response As VbMsgBoxResult
    Dim RowNo As Long
  

    With Me.lstsearch
'get the records row no
        RowNo = Val(.Column(.ColumnCount - 1, .ListIndex))
    End With
  
    If RowNo = 0 Then Exit Sub
  
'give the user a chance to change their mind
    Response = MsgBox("Are you sure that you want to delete this record", 292, "Delete Record")
    If Response = vbYes Then
      
        wsAirStock.Cells(RowNo, 1).EntireRow.Delete
  
'refresh the listbox
        lstsearch.RemoveItem (lstsearch.ListIndex)
      
        MsgBox "Record Deleted", 64, "Record Deleted"

    End If

  End Sub

Private Sub UserForm_Initialize()
    Set wsAirStock = ThisWorkbook.worksSheets("airstock")
End Sub

Note variable wsAirStock - this needs to sit at TOP of your forms code page OUTSIDE any procedure.

Solution untested but hope helps

Dave

 

[dmt32]

sorry, spotted fat finger problem after I posted

Private Sub UserForm_Initialize()
    Set wsAirStock = ThisWorkbook.Worksheets("airstock")
End Sub

Dave

 

[LegenDSlayeR]

Hi Dave,

Thank you, for your help i shall try this out and get back to you