Select row in listbox and find matching entry

[learningthings]

I have a listbox containing a list of transactions that happened in one instance, for example a sale and a refund.
Each transaction has an ID attached to it and is recorded on its own respective sheets. I want to create a button that can match the ID from the selected listbox entry to an entry on either the sales or refund worksheet and delete the entry.
Any ideas on how I could do this?

 

[DanteAmor]

I want to create a button that can match the ID from the selected listbox entry to an entry on either the sales or refund worksheet and delete the entry.

Could you answer the following questions:

• You can put here the instruction you use to load the data in the listbox.
• Is the ID unique?
• It must be in one of the 2 sheets?
• What are the two sheets called?
• In which column do you store the ID, is it the same column in the 2 sheets?

 

[learningthings]

Could you answer the following questions:

• You can put here the instruction you use to load the data in the listbox.
• Is the ID unique?
• It must be in one of the 2 sheets?
• What are the two sheets called?
• In which column do you store the ID, is it the same column in the 2 sheets?

This is the code I use to load the listbox, it is named lstRegister and loads the sheets "Register View" which just displays the transactions on that page.

With frmRegister
Dim ws As Worksheet
Dim rng As Range
Dim MyArray
Set ws = Sheets("RegisterView")
Set rng = ws.Range("A1:I" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

With .lstRegister
       .Clear
       .ColumnHeads = False
       .ColumnCount = rng.Columns.Count
        '~~> create a one based 2-dim datafield array
        MyArray = rng
         '~~> fill listbox with array values
         .List = MyArray
         '~~> Set the widths of the column here. Ex: For 5 Columns
         '~~> Change as Applicable
         .ColumnWidths = "70;70;70;70;70;70;70;70;70"
         .TopIndex = 0
         .ListIndex = .ListCount - 1
    End With

End With

Yes, the ID is unique to each transaction and must be located in one of the two sheets. The ID is stored in column A for both sheets. The two sheets are named "Sales" and "Refunds".
Thank you for your help!

 

[DanteAmor]

Try this.
Select the ID and press a button.
In a commandbutton put the following code:

Private Sub CommandButton1_Click()
  Dim f As Range
  Dim sh As Worksheet
  Dim shs As Variant
  Dim i As Long
  
  shs = Array("Sales", "Refunds")
  
  With lstRegister
    If .ListIndex = -1 Then
      MsgBox "Select a item"
      Exit Sub
    End If
    
    For i = 0 To UBound(shs)
      Set sh = Sheets(shs(i))
      Set f = sh.Range("A:A").Find(.List(.ListIndex, 0), , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        If MsgBox("ID on sheet: " & sh.Name & vbCr & _
                  "Do you want to delete", vbQuestion + vbYesNo) = vbYes Then
          f.EntireRow.Delete
          Exit For
        Else
          Exit For
        End If
      End If
    Next
    
    If f Is Nothing Then
      MsgBox "ID not found"
    End If
  End With
End Sub

 

[learningthings]

Try this.
Select the ID and press a button.
In a commandbutton put the following code:

Private Sub CommandButton1_Click()
  Dim f As Range
  Dim sh As Worksheet
  Dim shs As Variant
  Dim i As Long
 
  shs = Array("Sales", "Refunds")
 
  With lstRegister
    If .ListIndex = -1 Then
      MsgBox "Select a item"
      Exit Sub
    End If
   
    For i = 0 To UBound(shs)
      Set sh = Sheets(shs(i))
      Set f = sh.Range("A:A").Find(.List(.ListIndex, 0), , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        If MsgBox("ID on sheet: " & sh.Name & vbCr & _
                  "Do you want to delete", vbQuestion + vbYesNo) = vbYes Then
          f.EntireRow.Delete
          Exit For
        Else
          Exit For
        End If
      End If
    Next
   
    If f Is Nothing Then
      MsgBox "ID not found"
    End If
  End With
End Sub

Thank you so much! It worked wonderfully

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[learningthings]

I'm glad to help you. Thanks for the feedback.

Hi Dante! I had another quick question if you wouldn't mind.
I am now coding an edit button. The way each sale is recorded is through a form that details item, quantity, subtotal, etc.
I would like to be able to code the edit button to search the worksheets "Sales" and "Refunds" for the matching entry ID (similar to the delete button) but instead, pull up the form and have the relevant data prefilled. Then, if I hit save, it saves this edit.
What do you think?

 

[DanteAmor]

It's a different topic and a different macro. You could create a new thread.

 

[learningthings]

It's a different topic and a different macro. You could create a new thread.

Okay, will do! Thank you