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

MissDB

New Member
Joined
Mar 14, 2014
Messages
8
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.

Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,218,625
Messages
6,143,580
Members
450,493
Latest member
Woejeber

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
Back
Top