macro to delete table row/entry

[rjmdc]

hi
this is the code i am working with. i am missing something at the beginning because it doesnt allow for choice of the entry to delete

Sub DeleteRow()

 Call WSUnProtect(Worksheets("Check Queue"))
 
 MsgBox "Choose entry you want to delete."

    If Selection.Column <> 1 Or Selection.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
        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 = Selection.Rows(1).Row
    slr = sr - Selection.ListObject.Range.Row  'The starting List Row
    
    Selection.ListObject.ListRows(slr).Delete

    Call WSProtect(Worksheets("Check Queue"))
    
End Sub

 

[rjmdc]

Word of warning, with this at the top of the code

Call WSUnProtect(Worksheets("Check Queue"))

If the first message box comes up, or they select No on the 2nd one, your sheet will be unprotected.

 

[rjmdc]

hi fluff
i am a bit confused
i tried your code
if the unprotect isnt on the top then user cannot choose a cell as the entire sheet is protected
however it isnt easy to choose a cell in column a when executing your code, it first asks to delete wherever the cursor happens to be

 

[rjmdc]

anyway whats the 8 all about?

 

[Fluff]

If the entire sheet is locked down, then you cannot use exit sub in the code.
Try selecting No on the message box & see what happens.

 

[rjmdc]

hi
your macro allows a choice outside of column A

 

[Fluff]

Does that matter as long as the the selection is inside the table?

 

[rjmdc]

for sure as it is allowing deletion of the column titles

 

[Fluff]

It will not allow you delete the header row.

 

[rjmdc]

it did
what did i do wrong
i copied your code exactly

 

[Fluff]

It cannot delete the header row, because it's not part of the Data body range.
With this data

Postcode	In Use?	Latitude	Longitude	Easting	Northing	GridRef
S36 1GJ	Yes	53.472455	-1.601497	426549	397376	SK265973
OL4 4BA	Yes	53.546558	-2.059182	396176	405548	SD961055
DE4 2BU	Yes	53.145179	-1.664349	422548	360945	SK225609
BD6 3FH	Yes	53.767997	-1.803484	413051	430201	SE130302
BD20 6QX	Yes	AED	-1.952001	403253	442777	SE032427
HD3 3UH	Yes	53.659087	-1.853093	409806	418076	SE098180
HD7 3EB	No	AED	-1.826124	411609	409593	SE116095
DH8 5EJ	Yes	54.853973	-1.839415	410408	551031	NZ104510
DL8 5JZ	Yes	AED	-1.850724	409807	492011	SE098920
S10 3PH	Yes	AED	-1.539468	430738	386290	SK307862
DL8 3SH	Yes	AED	-2.200844	387027	489389	SD870893
HD3 3GS	Yes	53.659793	-1.842574	410501	418156	SE105181

If I selected any cell in A1:G1, this message box would be displayed

MsgBox "You need to select a cell within the table"