Feb 03 2021 03:45 AM - edited Feb 03 2021 03:46 AM
Hi. I want to create a spreadsheet and protect the sheet from editing. The user would be able to add data but not edit after that. Can someone assist?
Feb 03 2021 04:52 AM
SolutionIt's possible if you're willing to use VBA.
Step 1:
Step 2:
Step 3:
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 06:07 AM - edited Feb 03 2021 06:08 AM
Did you follow step 1 - unlocking cells? If you don't do that, all cells will be locked!
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:
Code:
'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.
Nikolino
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 04 2021 12:07 AM
@Hans Vogelaar Hi, yes I unlocked the cells but it locks all capturing of data as well. I want the user to add information but not edit the information.
Feb 04 2021 02:53 AM
Do you mean that the user should be able to add information in a cell that already has been filled before, but not edit the existing content?
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.
Feb 05 2021 03:28 AM
If you follow ALL the steps in my first reply, it will do exactly what you want.
Feb 05 2021 03:39 AM
@Hans Vogelaar thank you! I think I mist something when I did it the 1st time
Feb 03 2021 04:52 AM
SolutionIt's possible if you're willing to use VBA.
Step 1:
Step 2:
Step 3:
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