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

 

Resources