17

I want to stop others from editing the cell contents in my excel sheet using VBA. Is it possible to do this?

armstrhb
  • 4,054
  • 3
  • 20
  • 28
raam
  • 171
  • 1
  • 1
  • 3

5 Answers5

31

You can first choose which cells you don't want to be protected (to be user-editable) by setting the Locked status of them to False:

Worksheets("Sheet1").Range("B2:C3").Locked = False

Then, you can protect the sheet, and all the other cells will be protected. The code to do this, and still allow your VBA code to modify the cells is:

Worksheets("Sheet1").Protect UserInterfaceOnly:=True

or

Call Worksheets("Sheet1").Protect(UserInterfaceOnly:=True)
Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
  • 1
    You may also want to add a password, otherwise it's a trivial matter to just click "unprotect worksheet" and then you can make edits to the previously locked cells. – Jonathan Feb 19 '14 at 05:53
  • the line above Worksheets("Sheet1").Protect(UserInterfaceOnly:=True) should be Worksheets("Sheet1").Protect UserInterfaceOnly:=True , i.e. no parentheses – dinotom May 23 '16 at 19:21
  • @dinotom, look at the edit history and the guy who edited it that way explains why – Lance Roberts May 23 '16 at 22:40
  • @Lance Roberts... ok, I don;t know how to see the edit history but that syntax doesnt work – dinotom May 23 '16 at 23:00
  • Yeh, an oddity of VBA. You can use the parentheses if you use "Call". I'll edit. – Lance Roberts May 25 '16 at 16:55
4

Try using the Worksheet.Protect method, like so:

Sub ProtectActiveSheet()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Protect DrawingObjects:=True, Contents:=True, _
        Scenarios:=True, Password="SamplePassword"
End Sub

You should, however, be concerned about including the password in your VBA code. You don't necessarily need a password if you're only trying to put up a simple barrier that keeps a user from making small mistakes like deleting formulas, etc.

Also, if you want to see how to do certain things in VBA in Excel, try recording a Macro and looking at the code it generates. That's a good way to get started in VBA.

Ben McCormack
  • 32,086
  • 48
  • 148
  • 223
2

Let's say for example in one case, if you want to locked cells from range A1 to I50 then below is the code:

Worksheets("Enter your sheet name").Range("A1:I50").Locked = True
ActiveSheet.Protect Password:="Enter your Password"

In another case if you already have a protected sheet then follow below code:

ActiveSheet.Unprotect Password:="Enter your Password"
Worksheets("Enter your sheet name").Range("A1:I50").Locked = True
ActiveSheet.Protect Password:="Enter your Password"
Milan Sheth
  • 884
  • 12
  • 11
1

You can also do it on the worksheet level captured in the worksheet's change event. If that suites your needs better. Allows for dynamic locking based on values, criteria, ect...

Private Sub Worksheet_Change(ByVal Target As Range)

    'set your criteria here
    If Target.Column = 1 Then

        'must disable events if you change the sheet as it will
        'continually trigger the change event
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True

        MsgBox "You cannot do that!"
    End If
End Sub
Fink
  • 3,356
  • 19
  • 26
0
Sub LockCells()

Range("A1:A1").Select

Selection.Locked = True

Selection.FormulaHidden = False

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= False, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

End Sub
krusaint
  • 101
  • 4