Search and delete

LegenDSlayeR

New Member
Joined
Nov 26, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,791
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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
 
Solution

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,791
Office Version
  1. 2019
Platform
  1. Windows
sorry, spotted fat finger problem after I posted

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

Dave
 

LegenDSlayeR

New Member
Joined
Nov 26, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi Dave,

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

Forum statistics

Threads
1,141,400
Messages
5,706,212
Members
421,432
Latest member
yash0468

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top