How to Lock and Unlock Worksheets and Cells in VBA?

Updated: Feb 3, 2021

This part will go through how to protect your cells with VBA so that no clumsy user destroys your fine art.

Worksheet and Cell protection can make sure that your scripts are less likely to be destroyed by the user. However, don't overdue it in my opinion as it can make things rather annoying if every cell the user clicks on is protected. Focus on worksheet UX so that it is clear where the user should go and that the cell protection is only there as a second layer should the user go wrong.
 

Worksheet protection

'Protect a sheet
 Sheets("MySheet").Protect
 
 'Protect a sheet with a password
 Sheets("MySheet").Protect Password:="Password"
 
 'Unprotect a sheet
 Sheets("MySheet").Unprotect
 
 'Unprotect a sheet with a password
 Sheets("MySheet").Unprotect Password:="Password"
 

If you protect a sheet in the workbook the default protection will involve the following:

  • Contents – Excel gridview

  • Objects – Shapes and charts

  • Scenarios – For example 'What If scenarios'


 The default setting for the above will be True. Aside from these protections setting you can also set the following:
 
 expression.Protect (Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)
 
 Where expression is a variable that represents a worksheet object. For more information on these feel free to visit Microsoft.

​

Cell protection


 Whilst the worksheet is protected, unlocked items can still be edited. All cells will by default be locked, unless you specifically unlock them. These are the cells that you basically let the user modify.
 

'Unlock cells
 Sheets("MySheet").Range("A1").Locked = False
 
 'Lock cells
 Sheets("MySheet").Range("A1").Locked = True


 When you are working with locking and unlocking sheets and cells, you must either unlock the worksheet in order for your macro to run, and then lock it again. Not too hard, but alternatively when you lock it you can use the 'UserInterfaceOnly' syntax which is one of the options above. If you set this parameter to 'True', your macros can run just fine.

    1070
    0