When all rows are deleted in a table on a locked spreadsheet, some of the cells in the row that is left that were previously unlocked become locked

[dpaton05]

I have a spreadsheet that is locked to prevent users from modifying it. I have 3 rows in it at the moment and I can modify cells A:J of each row. I manually unlock the spreadsheet and look at the protection and A5:J7 is unlocked.

CSS_quoting_tool_33.28.xlsm
	A	B	C	D	E	F	G	H		J	K	L
4	Date	Purchase order #	Quote Ref #	Name	Service	Requesting Organisation	Caseworker Name	Allocated to		Price ex. GST	GST	Price inc. GST
5	01/01/2021		51302	Bob Child	Supervised Transport	My organisation	Max CaseWorker			108.9	$10.89	$119.79
6	08/01/2021		51302	Bob Child	Supervised Transport	My organisation	Max CaseWorker			57	$5.70	$62.70
7	10/01/2021		51302	Bob Child	Supervised Transport	My organisation	Max CaseWorker			87.8	$8.78	$96.58
Costing_tool

Cell Formulas
Range	Formula
K5:K7	K5	=IF(E5="Activities",0,[Price ex. GST]*0.1)
L5:L7	L5	=IF(E5="Activities",[@[Price ex. GST]],[GST]+[Price ex. GST])

I have a button to delete all the rows above my data and if pressed it deletes all the rows but of the one, blank row that is left, I can only select the cell in columns B and H, where it needs to be A:J.

I unlock the spreadsheet and copied the spreadsheet into here and you can see that the code locks all cells in the row except B and H.

CSS_quoting_tool_33.28.xlsm
	A	B	C	D	E	F	G	H		J	K	L
4	Date	Purchase order #	Quote Ref #	Name	Service	Requesting Organisation	Caseworker Name	Allocated to		Price ex. GST	GST	Price inc. GST
5											$0.00	$0.00
Costing_tool

Cell Formulas
Range	Formula
K5	K5	=IF(E5="Activities",0,[Price ex. GST]*0.1)
L5	L5	=IF(E5="Activities",[@[Price ex. GST]],[GST]+[Price ex. GST])

This is the code behind my delete button

Sub CostingDeleteAll()
    On Error GoTo Errhandler
    Costing.Unprotect Password:=ToUnlock
        Dim tbl As ListObject
       ' Dim cell As Range
            Set tbl = ThisWorkbook.Worksheets("Costing_tool").ListObjects("tblCosting")
            'Delete all table rows except first row
            With tbl.DataBodyRange
                If .Rows.Count > 1 Then
                    .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
                End If
                'Clear the contents, but not delete the formulas
                Dim rng As Range
                Set rng = Costing.Range("A5:L5")
                    rng.Cells.SpecialCells(xlCellTypeConstants).Clear
             
               ' For Each cell In tbl.ListRows(1).Range.Cells
                '    If Not cell.HasFormula Then
                 '       cell.Value = ""
                  '  End If
                'Next
            End With
    Costing.Range("A%:J5").Locked = False
 
    Costing.Protect Password:=ToUnlock
Errhandler: If Err.Number = 1004 Then Exit Sub
End Sub

If I step through the sub, this is the extra code that it runs.

Private Sub Worksheet_Change(ByVal Target As Range)
Costing.Unprotect Password:=ToUnlock

    Dim ans As String
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Application.EnableEvents = False
    'If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then
        Application.EnableEvents = True
        Exit Sub
    End If
 
        If Target.Value < Date Then
            ans = MsgBox("This input is older than today !....Are you sure that is what you want ???", vbYesNo)
        If ans = vbNo Then Target.Value = ""
        End If
    End If

    Application.EnableEvents = True
Costing.Protect Password:=ToUnlock
End Sub

This is at the top of module 1 and stores the password

Option Explicit
Public Const ToUnlock As String = "My Password"

Can someone help me with why the range A5 :J5 in my table having all but 2 cells locked when I run the CostingDeleteAll sub please?

 

[dpaton05]

I found the problem, I had Costing.Range("A%:J5").Locked = False where it should have been Costing.Range("A5:J5").Locked = False

 

[Michael M]

Great to see your debugging is improving...well done..

 

[dpaton05]

Thanks Michael