Feb 03 2021 03:45 AM - edited Feb 03 2021 03:46 AM
Feb 03 2021 04:52 AMSolution
It's possible if you're willing to use VBA.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Me.Unprotect Password:="secret" For Each rng In Target If rng.Value <> "" Then rng.Locked = True End If Next rng Me.Protect Password:="secret" End Sub
Feb 03 2021 05:52 AM
@Janedb Another option is to use a Form to add entries. If other people already are intended to access it then it is possible/likely that you have the Excel file online (i.e. in Sharepoint) and you could create a Sharepoint Form to linked to the sheet and add the new entries as needed. Note you can also do this on the desktop version using VBA.
Feb 03 2021 05:58 AM
@Hans Vogelaar Thank you for your answer. I tried the macro but it does not even allow them to enter any data after the code. I actually want all users to be able to add information but not to edit it after they entered the data. This is for commission purposes.
Feb 03 2021 07:05 AM
If it is a matter of removing (not retrieving) a Worksheet password, the following code may be used:
In a common Module we paste the following code that removes the code from the activated sheet:
'CAUTION!! Recommended only for proprietary Excel sheets!
Option Explicit Sub GetPass() Const a = 65, b = 66, c = 32, d = 126 Dim i#, j#, k#, l#, m#, n#, o#, p#, q#, r#, s#, t# With ActiveSheet If .ProtectContents Then On Error Resume Next For i = a To b For j = a To b For k = a To b For l = a To b For m = a To b For n = a To b For o = a To b For p = a To b For q = a To b For r = a To b For s = a To b For t = c To d .Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & _ Chr(n) & Chr(o) & Chr(p) & Chr(q) & Chr(r) & Chr(s) & Chr(t) Next t Next s Next r Next q Next p Next o Next n Next m Next l Next k Next j Next i MsgBox "Finished" End If End With End Sub
The time it will take depends to a large extent on the power of the computer.
I would be happy to know if I could help.
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Feb 05 2021 02:35 AM
@Hans Vogelaar no, if the user added information to a cell, they should not be able to edit the information afterwards. This is specifically when someone enters their name to a cell for a commission, then another user can not edit it and put their name in for commission.