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