VBA to allow user to enter a code and then the relevant row in a table with that code be deleted

[MissDB]

Hi,

I have a table with rows of data in it. Each row has a unique reference no' which can be found in column 3. I want the user of the spreadsheet to be able to click on a button that says "Delete Student", which then brings up a message box (or form whichever is easier) where they can type in the reference no. and then click on delete, I'd like there then to be an extra step at this point which says are you sure you want to delete, then they can click Cancel or Ok.

Thanks
MissDB

 

[dmt32]

Hi,
see if this code does what you want.

Place in standard module & assign to your button. Adjust code where shown in RED as required.

Sub DeleteStudentRecord()


    Dim cell As Range
    Dim StudentID As Variant, col As Variant
    Dim sDefault As Variant
    Dim ws As Worksheet


    'the worksheet that contains your data
    'amend as required
    Set ws = Worksheets("Sheet1")


    'the column that contains
    'student ID - amend as required
    col = "C"




    Do
        StudentID = InputBox("Enter Student ID", "Delete Student Record", sDefault)
        If StrPtr(StudentID) = 0 Then Exit Do
        
        If Len(StudentID) = 0 Then


            MsgBox "You must enter a valid Student ID.", 48, "Error"


        Else


            Set cell = ws.Columns(col).Find(What:=StudentID, _
                                            LookIn:=xlValues, _
                                            LookAt:=xlWhole, _
                                            SearchOrder:=xlByRows, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=False, _
                                            SearchFormat:=False)


            If cell Is Nothing Then


                msg = MsgBox("Student ID: " & StudentID & Chr(10) & _
                             "Record Not Found" & Chr(10) & Chr(10) & _
                             "Do You Want to Try Again?", 37, "Record Not Found")
                If msg = 2 Then Exit Do
            Else


                msg = MsgBox("Student ID: " & StudentID & Chr(10) & _
                             "Confirm Record To Be Deleted.", 35, "Delete Record")
                If msg = 2 Then
                    'cancel pressed
                    Exit Do
                ElseIf msg = 6 Then
                    'Yes Pressed
                    'Delete Record
                    cell.EntireRow.Delete Shift:=xlUp
                    'Inform User
                    MsgBox "Student ID: " & StudentID & Space(20) & Chr(10) & _
                           "Record Deleted.", 48, "Record Deleted"


                    Exit Do
                Else
                    'No Pressed
                    sDefault = StudentID
                    'show inputbox
                End If
            End If
        End If
    Loop
End Sub

Hope Helpful

Dave

 

[MissDB]

Thank you Dave, I will have a go with this ASAP and get back to you!

MisSDB