In most cases, the macro protection that I see in many projects looks like this:
Worksheets("Sheet1").Unprotect Password:="123" 'here the macro does the action Worksheets("Sheet1").Protect Password:="123" That is, the author of the file is forced to set protection against playful user handles, but in order for his macro to perform the necessary actions, he has to temporarily remove it and then turn it on again. This technique works, but is far from perfect. Firstly, it is inconvenient and requires the introduction of such constructs in each of your macros. Secondly, if the macro execution is interrupted with an error, the sheet will remain unprotected.
There is a much easier and more beautiful way to solve the problem.
Press Alt+F11 to get into the Visual Basic Editor. Then find in the upper left corner in the Project Explorer window (if it is not visible, then click Ctrl + R) module This book (ThisWorkbook) and open with a double click:
Copy this code there:
Private Sub Workbook_Open() 'Enable protection of the first sheet for the user, but not the macro Worksheets("Sheet1"). ").EnableOutlining = True Worksheets("Sheet123").Protect Password:="2", UserInterfaceOnly:=True End Sub This procedure will automatically run when you open the file and put protection on the specified sheets of the book. Moreover, the parameter UserInterfaceOnly, which we additionally entered, tells Excel that the protection should not extend to actions performed by the macro, but only to user operations. For the second sheet, it’s still more fun – a line with a parameter EnableOutlining allows you to use grouping (plus-minus symbols to collapse-expand rows and columns) on a protected sheet.
Only three lines of code, but how convenient!
- What are macros and where to copy their code
- How to protect a sheet, book or entire file