Jump To:

• Unprotecting and Protecting the Worksheets
• Unprotecting and Protecting Workbooks

Unprotecting and Protecting the Worksheets:

As a VBA programmer you will often have protected sheets where you would like your program to be able to modify but still remain protected from the users. There are different methods for accomplishing this. In the method I will explain a line of code is added to the start of the program which unprotects the sheets and another line of code is added add the end just before program execution ends to protect the sheets again:

Sub Main()

‘This is to prevent the sheets from flickering
Application.ScreenUpdating = False

‘unprotects the current sheet ( sheet1), where “123” is the password
Unprotect (“123”)
Sheet2.Unprotect (“123”)
Sheet3.Unprotect (“123”)

‘your code

Application.ScreenUpdating = True

‘protects the sheets again
Unprotect (“123”)
Sheet2.Unprotect (“123”)
Sheet3.Unprotect (“123”)

End Sub

In the code above I also two lines of code to change the Application.ScreenUpdating state. By setting Application.ScreenUpdating to false none of the changes your program makes to the sheets will be visible to the user. By reassigning the value to true, all the changes will be made visible. Not only will this improve the programs performance, but it will also prevent screen flickering.

There may be times that If it is vital that the end user does not see the password in your code (“123” in this case). If this is the case you can lock your macro from viewing:

Unprotecting and Protecting Workbooks:

If its the workbook that has been protected you can use the same concept above to unprotect the workbook:

Sub Main()

ThisWorkbook.Unprotect (“123”)
‘your code
ThisWorkbook.Protect (“123”)

End Sub