Forum Discussion

Janedb's avatar
Janedb
Iron Contributor
Feb 03, 2021
Solved

Excel worksheet protect from editing in excel

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?

excel
  • HansVogelaar's avatar
    HansVogelaar
    Feb 03, 2021

    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.

10 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Feb 03, 2021

    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.

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    Feb 03, 2021

    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. 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 03, 2021

    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's avatar
      Janedb
      Iron Contributor
      Feb 03, 2021

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

Resources