Nov 12 2021 02:33 PM
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
Thanks
Nov 13 2021 02:51 AM
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
Nov 13 2021 10:40 AM
@NikolinoDE
Thanks for your reply.
But its not working, Please refer to the attached workbook
Nov 13 2021 11:05 AM
Hope I was able to help you with these sample files :).
NikolinoDE
I know I don't know anything (Socrates)
Nov 13 2021 11:11 AM
@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
Thanks
Nov 13 2021 11:31 AM
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.
Nov 13 2021 06:27 PM
Nov 14 2021 03:43 AM
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
Loop
r2 = r1 + 2
'Loop 2
Do While Cells(r2, c) <> ""
Rows(r2 + 1).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
r2 = r2 + 1
Loop
Application.EnableEvents = True
End Sub
Nov 14 2021 07:49 PM
Nov 15 2021 06:39 AM