"Delete Selected" in Search Result listbox deletes first row, instead of selected row.

[Faustus]

Excel 2010
VBA experience: Very Junior but not quite beginner
Userform purpose is: Data Entry tool with a search /update /Delete function.
Supplied example is stripped of private information and beauty :-) to isolate code/functionality issues.
(please excuse in-code scribblings and deactivated non functional items)

*search fills listbox1 with matching results
selecting result should allow user to view data from that row in Userform and either update or delete that row.Stripped_dev.xlsmStripped_dev.xlsm

Issue: Delete function (after searched and selected from listbox of results) does not delete selected (activecell) row. Rather it always deletes first row of data. I have tried numerous alternative methods to get the selection of the row in the listbox to set C as the activecell similar to the "amend" code, but it just blows up and errors. So I'm leaving it somewhat in it's initial partially functional state. (Note: if you enter the exact query search, as in an exact "abc-002" vs. a * search like " abc* " it will set the correct activecell and delete it. But that kinda defeats the purpose of a search function. )

Private Sub cmbDelete_Click()
    Dim msgResponse As String    'confirm delete
    Application.ScreenUpdating = True
    'get user confirmation
    msgResponse = MsgBox("This will delete the selected record. Continue?", _
                         vbCritical + vbYesNo, "Delete Entry")
    Select Case msgResponse    'action dependent on response
        Case vbYes
            'c has been selected by Find button
            Set c = ActiveCell
            c.EntireRow.Delete    'SHOULD But does not remove selected entry by deleting row
                                 'instead always deletes first row of data, aka Row 8
            'restore form settings
            With Me
                .cmbAmend.Enabled = False    'prevent accidental use
                .cmbDelete.Enabled = False    'prevent accidental use
                .cmbAdd.Enabled = True    'restore use
                'clear form
                ClearControls
            End With

        Case vbNo
            Exit Sub    'cancelled
    End Select
    Application.ScreenUpdating = True
End Sub

The amend/update function successfully amends the selected (activecell) row after selecting the results from a * search. scratching head as to how to get the activecell status similar to the amend/update function to work with the Delete.

Private Sub cmbAmend_Click()
    Dim Rw As Range
    Dim NumRows As Long
    Application.ScreenUpdating = False
    If Me.ListBox1.ListIndex <> -1 Then
        NumRows = 0
        For Each Rw In rng.Rows
            If NumRows = r Then
                Set c = Rw.Cells(1)
                Exit For
            End If
            NumRows = NumRows + 1
        Next Rw
    Else
        Set c = ActiveCell
    End If
    c.Value = Me.TextBox1.Value ' write amendments to database
    c.Offset(0, 1).Value = Me.TextBox2.Value
     
    c.Offset(0, 3).Value = Me.TextBox3.Value
    c.Offset(0, 4).Value = Me.TextBox4.Value
    c.Offset(0, 5).Value = Me.TextBox5.Value
    c.Offset(0, 6).Value = Me.TextBox6.Value
    c.Offset(0, 7).Value = Me.TextBox7.Value
    c.Offset(0, 8).Value = Me.TextBox8.Value
    c.Offset(0, 9).Value = Me.TextBox9.Value
    c.Offset(0, 10).Value = Me.TextBox10.Value
    c.Offset(0, 11).Value = Me.TextBox11.Value
    c.Offset(0, 12).Value = Me.TextBox12.Value
    c.Offset(0, 13).Value = Me.TextBox13.Value
    c.Offset(0, 14).Value = Me.TextBox14.Value
     c.Offset(0, 15).Value = Me.TextBox15.Value
     c.Offset(0, 16).Value = Me.TextBox16.Value
     c.Offset(0, 17).Value = Me.TextBox17.Value
     c.Offset(0, 18).Value = Me.TextBox18.Value
     c.Offset(0, 19).Value = Me.TextBox19.Value
     c.Offset(0, 20).Value = Me.TextBox20.Value
     c.Offset(0, 21).Value = Me.TextBox21.Value
     c.Offset(0, 22).Value = Me.TextBox22.Value
     c.Offset(0, 23).Value = Me.TextBox23.Value
     c.Offset(0, 24).Value = Me.TextBox24.Value
     c.Offset(0, 25).Value = Me.TextBox25.Value
     c.Offset(0, 26).Value = Me.TextBox26.Value
     c.Offset(0, 27).Value = Me.TextBox27.Value
     'c.Offset(0, 28).Value = Me.TextBox28.Value
     'c.Offset(0, 10).Value = Me.TextBox10.Value
     'c.Offset(0, 10).Value = Me.TextBox10.Value
     
     
     
     'restore Form
    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = vbNullString
        .TextBox2.Value = vbNullString
        .TextBox3.Value = vbNullString
        .TextBox4.Value = vbNullString
        .TextBox5.Value = vbNullString
        .TextBox6.Value = vbNullString
        .TextBox7.Value = vbNullString
        .TextBox8.Value = vbNullString
        .TextBox9.Value = vbNullString
        .TextBox10.Value = vbNullString
        .TextBox11.Value = vbNullString
        .TextBox12.Value = vbNullString
        .TextBox13.Value = vbNullString
        .TextBox14.Value = vbNullString
        .TextBox15.Value = vbNullString
        .TextBox16.Value = vbNullString
        .TextBox17.Value = vbNullString
        .TextBox18.Value = vbNullString
        .TextBox19.Value = vbNullString
        .TextBox20.Value = vbNullString
        .TextBox21.Value = vbNullString
        .TextBox22.Value = vbNullString
        .TextBox23.Value = vbNullString
        .TextBox24.Value = vbNullString
        .TextBox25.Value = vbNullString
        .TextBox26.Value = vbNullString
        .TextBox27.Value = vbNullString
        '.TextBox10.Value = vbNullString
        
        .ListBox1.Clear
        .Height = frmHt
        .Width = frmWidth
    End With
    If Sheet1.AutoFilterMode Then Sheet1.Range("A8").AutoFilter
    Application.ScreenUpdating = True
    On Error GoTo 0
End Sub

This has been cobbled together using a number of different templates and my own limited vba to get the functionality desired . (I feel obliged to make clear that I am not the Originator of the majority of the cod. So now I'm trying to build off functional models. I made attempts first and don't think I was even close so most of the good stuff isn't mine. Meaning, there are parts of this that I really do not claim to fully understand how exactly it works. I thought I had a clue, but clearly not so much.)

I do have a list of functionality that I would like to add, but I think the Delete function is fairly key to lock down before trying to add bells and whistles.

Other things I need to figure out just in case someone has a solution/code/link to offer:

1.Protect the spreadsheet from direct access (not using Userform) to all but one or two usesr. (force non admins users to use Userform only, but allow admin to easily access sheet directly when needed.. ? Userlevel password protection)

2. if userlevel passwords is possible, setting up users auto signature. (like a database where a user logs in and all edits by that user are associated with that user (i.e. automatically show "input by xxxx" on "date" ))

3. additional "Search by Due date" function. (this might be overkill, but the ideal function would be to be able to set a search based on one column "Due Date" and return AND OUTPUT a listing of all rows "due between date A and date B".

I'm sure there are more, but I don't want to get off target. Before I worry about any of that, I have to get the Delete to work. Else I will need to change my gameplan.

Thanks in advance.

[Faustus]

Hey all,.. I believe I have found my critical error and I may need to start from scratch. (relatively)
I will try again,

I wouldn't waste any extra time on this one as it might need to be done a different way.

I would still be interested in the other issues, as I will likely want to accomplish them once I get the basic functionality up and running.

Thanks.