How to Lock the data in a cell in excel using vba
I want to stop others from editing the cell contents in my excel sheet using VBA. Is it possible to do this?
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:
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.
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"
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
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