How to Lock the data in a cell in excel using vba

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[vba] How to Lock the data in a cell in excel using vba

Examples related to vba

Examples related to excel