macro to delete table row/entry

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
261
Office Version
365
Platform
Windows
thank you
you are correct, although it can be chosen it cannot be executed to delete
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
That's right.
There is nothing wrong with the code that Mumps' supplied, except you will need to change the Exit Sub lines if you want to reprotect the sheet.
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
261
Office Version
365
Platform
Windows
at the end before exit sub i added wsProtect
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
If you just have that at the end your sheet can be left unprotected, when you get the message asking if you want to delete the entry select No & see what happens.
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
261
Office Version
365
Platform
Windows
so what would be a solution? can i add a protect if msgbox = no? and how would i do that?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
Easiest option is to call the protect macro just before any line that says "Exit Sub"
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
261
Office Version
365
Platform
Windows
thanks
nearly there
what do i do to return to sub instead of exit if choice was not in column a - how do i go back to choose what you want to delete?
VBA Code:
Sub DeletePrintCheck()
    Call WSUnProtect(Worksheets("Check Queue"))
    Dim rng As Range
    Set rng = Application.InputBox(prompt:="Select entry you want to delete.", Type:=8)
    If rng.Column <> 1 Or rng.Cells.Count <> 1 Then
        MsgBox "You must be in Column A to perform the delete function."
        Exit Sub
    End If
    
    If MsgBox("Are you sure you want to delete: " & Selection.Value & "?", vbYesNo + vbExclamation, "Confirm Delete") = vbNo Then
        Call WSProtect(Worksheets("Check Queue"))
        Exit Sub
    End If
        Call WSUnProtect(Worksheets("Check Queue"))
    Dim tbl As ListObject, LastRow As Range
    Dim col As Long
    Set tbl = Worksheets("Check Queue").ListObjects("tblCheckQueue")
    Dim sr As Long 'Actual Row
    Dim slr As Long 'Start List Row
    sr = rng.Rows(1).Row
    slr = sr - rng.ListObject.Range.Row  'The starting List Row
    rng.ListObject.ListRows(slr).Delete
        Call WSProtect(Worksheets("Check Queue"))
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,351
Messages
5,486,363
Members
407,542
Latest member
Tyronaught

This Week's Hot Topics

Top