Autolocking a table


Hi All,

I am autolocking a cell when typing a value in the cell, But when i am doing this the table sometimes in my workbook is not expanding, Can anyone help me for the same. For more please refer to the attached workbook. Let me know in case of any confusions


9 Replies



Untested ... hope it works :))


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="xyz"
Target.Locked = True
With ActiveSheet
    .Protect userinterfaceonly:=True, Password:="xyz", AllowInsertingColumns:=True, AllowInsertingRows:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
End Sub



Thanks for your reply.

But its not working, Please refer to the attached workbook


Hope I was able to help you with these sample files .



I know I don't know anything (Socrates)


@NikolinoDE Thanks again for helping me,

The concern here is i want tonly freshly cells to be auto locked and in the attached workbook when i added C6 and C7 cell it worked, But after that it didn't worked for cell C8
Can you please check if the same is happening with you as well?
I am using Office 365 with windows, Not sure if that matters


So it works for me. If I enter a value in cell, it locks immediately afterwards.


Maybe another user can try it here in the forum and give us feedback?

Tried with another Excel / Pc?

In the end maybe update everything and do an office repair.


*I tested the file with Excel 2016.


Hope I was able to help you with this info.


Thanks for your reply, May be there is a confusion, I am not talking about getting a cell locked, That i was able to do with the first code as well, But i am talking about being get locked and be a part of table i mean a row gets added in the table if we write something below the table
So it should work like every time we write something below the table it should be a part of table and then immediately gets locked


You want the table to expand by one line, as far as I understand.

Think that this code will help you, you only have to insert your code into it

(as far as locking cells after use). I tried the code via insert line in the table and it worked.

Add the value in the bottom line of the table and enter it into the table.

I leave both of them to you :).

File with only "insert lines" ... is inserted.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Set Target = Application.Intersect(Target, Columns("J:N"))

Dim r1 As Integer
Dim r2 As Integer
Dim c As Integer

c = 2
r1 = 15

'Loop 1
Do While Cells(r1, c) <> ""
    Rows(r1 + 1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    r1 = r1 + 1

r2 = r1 + 2

'Loop 2
Do While Cells(r2, c) <> ""
    Rows(r2 + 1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    r2 = r2 + 1
Application.EnableEvents = True
End Sub





Hi Thanks for your reply

Just wanted to make you understand one more thing that table could be anywherre in the workbook and i will have to add rows and columns frequently, So please let me know if it works for both of them in all the ranges, As far as i casee you have dded specific ranges for the same
Maybe another specialist can help here
... my knowledge is limited as far as tables or pivot possibilities with VBA are concerned.

Thank you for your patience and time so far.