SOLVED

Excel worksheet protect from editing in excel

Brass Contributor

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?

10 Replies
best response confirmed by Janedb (Brass Contributor)
Solution

@Janedb 

It's possible if you're willing to use VBA.

Step 1:

  • Select all the cells that the user should be able to fill.
  • Press Ctrl+1 to activate the Format Cells dialog.
  • Activate the Protection tab.
  • Clear the Locked check box.
  • Click OK.

Step 2:

  • Activate the Review tab of the ribbon.
  • Click 'Protect Sheet'.
  • Specify what the user should be able to do.
  • At the very least, leave the check box 'Select unlocked cells' ticked.
  • If desired, specify a password that will be needed to unlock the sheet. If you do, make sure you remember it!
  • Click OK.
  • If you specified a password, you'll have to enter it again as confirmation.

Step 3:

  • Right-click the sheet tab.
  • Select 'View Code' from the context menu.
  • Copy the following code into the worksheet module:
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
  • Replace "secret" with the password you used; if you didn't specify a password, use "".
  • Switch back to Excel.
  • Save the workbook as a macro-enabled workbook (.xlsm).
  • Make sure that the user allows macros when they open the workbook.

@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. 

@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.

@Janedb 

Did you follow step 1 - unlocking cells? If you don't do that, all cells will be locked!

@Janedb 

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.

 

@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.

@Janedb 

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?

@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.

@Janedb 

If you follow ALL the steps in my first reply, it will do exactly what you want.

@Hans Vogelaar thank you! I think I mist something when I did it the 1st time

1 best response

Accepted Solutions
best response confirmed by Janedb (Brass Contributor)
Solution

@Janedb 

It's possible if you're willing to use VBA.

Step 1:

  • Select all the cells that the user should be able to fill.
  • Press Ctrl+1 to activate the Format Cells dialog.
  • Activate the Protection tab.
  • Clear the Locked check box.
  • Click OK.

Step 2:

  • Activate the Review tab of the ribbon.
  • Click 'Protect Sheet'.
  • Specify what the user should be able to do.
  • At the very least, leave the check box 'Select unlocked cells' ticked.
  • If desired, specify a password that will be needed to unlock the sheet. If you do, make sure you remember it!
  • Click OK.
  • If you specified a password, you'll have to enter it again as confirmation.

Step 3:

  • Right-click the sheet tab.
  • Select 'View Code' from the context menu.
  • Copy the following code into the worksheet module:
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
  • Replace "secret" with the password you used; if you didn't specify a password, use "".
  • Switch back to Excel.
  • Save the workbook as a macro-enabled workbook (.xlsm).
  • Make sure that the user allows macros when they open the workbook.

View solution in original post