Log In
Register
Home
August 21, 2019
Offline
Hi,
We can protect formula, columns, particular cell in Excel, however I got a Excel Table scenarios whereby user is able to continue data entry but the last column (formula) need to be lock (See Attached)
I highlighted the whole worksheet unchecked locked, select the formulas in table (CTRL 1, check the locked, goto Review and protect worksheet)
and when I input S/No 3, the formula no longer propagate down automatically to next row
Is there is way to fulfill this, at the same time lock the formula cell and the formula still able to bring down to next row during data entry
Thank you
See Answer
Trusted Members
December 20, 2019
Offline
Needs a bit more work but you could do it with a small Macro
Sub UpdateFormula2()
Dim LastRow As Integer
Dim tbl As ListObject
ActiveSheet.Unprotect Password:="X"
Set tbl = ActiveSheet.ListObjects("Table1")
tbl.Resize tbl.Range.CurrentRegion
ActiveSheet.Protect Password:="X"
End Sub
Answers Post
August 21, 2019
Offline
Hi Purfleet, your VBA is alien to me， can explain the meaning of
Set tbl = ActiveSheet.ListObjects("Table1")
tbl.Resize tbl.Range.CurrentRegion
why only the formula row is protect ?
So this is not possible for other excel method ?
=========================================================
My version as follows:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet1.Unprotect "1234"
If Target.Column = 1 Or Target.Column = 3 Or Target.Column = 5 Then
If VBA.IsEmpty(Target.Value) Then
Target.Locked = False
Else
Target.Locked = True
End If
Sheet1.Protect "1234"
End If
End Sub
Trusted Members
December 20, 2019
Offline
Set tbl = ActiveSheet.ListObjects("Table1") - is just setting tbl as a short cut for the next line
tbl.Resize tbl.Range.CurrentRegion - this resets the rows in the orginal table to pick up the new rows
The code i pasted in locks the whole page, cells are locked by default once protection is turned on. So i unlocked the input cells and then locked and unlocked the whole page
Not sure what this part of your post means
why only the formula row is protect ?
So this is not possible for other excel method ?
1 Guest(s)
