Delete row source from selected line in ListBox

**PinkMafia14** · 02-12-2013, 05:26 AM

OK, several problems here...

In my UserForm when I click on "Find"...

1. ListBox not filling in properly
What should appear for the row source in question (in a row from left to right) is:
Column A (TextBox1), then
Column B (TextBox2), then
Column H (TextBox3)
Column I (TextBox4)
Column J (TextBox5)
Column K (ComboBox6)
Column L (Combobox7)
Column M (TextBox8)
Column O (TextBox11)

2. Selecting item in ListBox doesn't select row source
When I click on an item in the listbox, I should not just fill in the relevant TextBoxes / ComboBoxes with the row source, it should go to the row source in the sheet, that way when I delete an item, it doesn't just delete the first line in the listbox (even when this is not the selected line).

3. Deleting rows - deletes first item in listbox, not selected item
Same problem as above, but not sure if I need to fix the code for "delete" or the code for "listbox click." Basically, when I click on "delete" button, I want to delete the row in the worksheet that corresponds to the selected line in the listbox. What is happening now is that when I click on "Find," the active cell defaults to the first item found and not the selected item in the listbox. Gosh. Hope I'm explaining correctly!!!!

Help, please!!! Below are the codes.

Private Sub ListBox1_Click()
If Me.ListBox1.ListIndex = -1 Then 'not selected
MsgBox " No selection made"
ElseIf Me.ListBox1.ListIndex >= 0 Then 'User has selecter
r = Me.ListBox1.ListIndex

With Me
    .TextBox1.Value = ListBox1.List(r, 0)
    .TextBox2.Value = ListBox1.List(r, 1)
    .TextBox3.Value = ListBox1.List(r, 2)
    .TextBox4.Value = ListBox1.List(r, 3)
    .ComboBox6.Value = ListBox1.List(r, 5)
    .ComboBox7.Value = ListBox1.List(r, 6)
    .TextBox8.Value = ListBox1.List(r, 7)
    .TextBox11.Value = "Cash"
  .cmbAdd.Enabled = False
  .cmbDelete.Enabled = True
End With
End If
    
End Sub

Sub FindAll()
    Dim strFind As String    'what to find
    Dim rFilter As Range     'range to search
        
   With ActiveSheet
    On Error Resume Next
    
    Set rng = .Range("o402:o500")
    strFind = "Cash"
        
    On Error GoTo 0
        Me.ListBox1.Clear
        For Each c In rng
        If c.Value = strFind Then
            With Me.ListBox1
                .AddItem c.Value
                .List(.ListCount - 1, 0) = c.Offset(0, -14).Value
                .List(.ListCount - 1, 1) = c.Offset(0, -13).Value
                .List(.ListCount - 1, 2) = c.Offset(0, -7).Value
                .List(.ListCount - 1, 3) = c.Offset(0, -6).Value
                .List(.ListCount - 1, 4) = c.Offset(0, -5).Value
                .List(.ListCount - 1, 5) = c.Offset(0, -4).Value
                .List(.ListCount - 1, 6) = c.Offset(0, -3).Value
                .List(.ListCount - 1, 7) = c.Offset(0, -2).Value
                
                                               
            End With
            End If
        Next c
    End With
End Sub

Private Sub FindButton1_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim rSearch As Range  'range to search
    Dim f      As Integer

    Columns("o").EntireColumn.Hidden = False
    strFind = "Cash"    'what to look for
    With ActiveSheet
        Set rSearch = .Range("O402", .Range("O65000").End(xlUp))
    End With

    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me    'load entry to form
                .TextBox1.Value = c.Offset(0, -14).Value
                .TextBox2.Value = c.Offset(0, -13).Value
                .TextBox3.Value = c.Offset(0, -7).Value
                .TextBox4.Value = c.Offset(0, -6).Value
                .TextBox5.Value = c.Offset(0, -5).Value
                .ComboBox6.Value = c.Offset(0, -4).Value
                .ComboBox7.Value = c.Offset(0, -3).Value
                .TextBox8.Value = c.Offset(0, -2).Value
                .TextBox11.Value = c.Offset(0, 0).Value
                .cmbAdd.Enabled = False      'don't want to duplicate record
                f = 0
            End With
            
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            
            If f > 1 Then
                Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")

                    Case vbOK
                        FindAll
                    Case vbCancel
                        'do nothing
                End Select
                Me.Height = frmMax

            End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With
Columns("o").EntireColumn.Hidden = True
End Sub

Private Sub FindButton1_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim rSearch As Range  'range to search
    Dim f      As Integer

    Columns("o").EntireColumn.Hidden = False
    strFind = "Cash"    'what to look for
    With ActiveSheet
        Set rSearch = .Range("O402", .Range("O65000").End(xlUp))
    End With

    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me    'load entry to form
                .TextBox1.Value = c.Offset(0, -14).Value
                .TextBox2.Value = c.Offset(0, -13).Value
                .TextBox3.Value = c.Offset(0, -7).Value
                .TextBox4.Value = c.Offset(0, -6).Value
                .TextBox5.Value = c.Offset(0, -5).Value
                .ComboBox6.Value = c.Offset(0, -4).Value
                .ComboBox7.Value = c.Offset(0, -3).Value
                .TextBox8.Value = c.Offset(0, -2).Value
                .TextBox11.Value = c.Offset(0, 0).Value
                .cmbAdd.Enabled = False      'don't want to duplicate record
                f = 0
            End With
            
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            
            If f > 1 Then
                Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")

                    Case vbOK
                        FindAll
                    Case vbCancel
                        'do nothing
                End Select
                Me.Height = frmMax

            End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With
Columns("o").EntireColumn.Hidden = True
End Sub

Private Sub cmbDelete_Click()
    Dim msgResponse As String 'confirm delete
    Application.ScreenUpdating = False
    'get user confirmation
    msgResponse = MsgBox("This will delete the selected row.  Are you sure you want to remove this transaction?", vbCritical + vbYesNo, "Delete Entry")
    Select Case msgResponse 'action depends on response
        Case vbYes
        'c has been selected by Find Button
        Set c = ActiveCell
        c.EntireRow.Delete 'remove entry by deleting row
        'restore form settings
        With Me
        .cmbAdd.Enabled = True
        .cmbDelete.Enabled = False
    End With
    
    Case vbNo
        Exit Sub
    End Select
Application.ScreenUpdating = True
Unload Me

End Sub