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

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEFGHJKL
4DatePurchase order #Quote Ref #NameServiceRequesting OrganisationCaseworker NameAllocated toPrice ex. GSTGSTPrice inc. GST
501/01/202151302Bob ChildSupervised TransportMy organisationMax CaseWorker108.9$10.89$119.79
608/01/202151302Bob ChildSupervised TransportMy organisationMax CaseWorker57$5.70$62.70
710/01/202151302Bob ChildSupervised TransportMy organisationMax CaseWorker87.8$8.78$96.58
Costing_tool
Cell Formulas
RangeFormula
K5:K7K5=IF(E5="Activities",0,[Price ex. GST]*0.1)
L5:L7L5=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
ABCDEFGHJKL
4DatePurchase order #Quote Ref #NameServiceRequesting OrganisationCaseworker NameAllocated toPrice ex. GSTGSTPrice inc. GST
5$0.00$0.00
Costing_tool
Cell Formulas
RangeFormula
K5K5=IF(E5="Activities",0,[Price ex. GST]*0.1)
L5L5=IF(E5="Activities",[@[Price ex. GST]],[GST]+[Price ex. GST])


This is the code behind my delete button
VBA Code:
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.

VBA Code:
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
VBA Code:
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?
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I found the problem, I had Costing.Range("A%:J5").Locked = False where it should have been Costing.Range("A5:J5").Locked = False
 
Solution

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,520
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Great to see your debugging is improving...well done..(y)(y)
 

Forum statistics

Threads
1,136,276
Messages
5,674,790
Members
419,525
Latest member
mountainkids

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
Top