VBA Excel Working With Protected Sheets

Jump To:

Contents

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:

 

Setting a password for you VBA Code

Setting a password for your VBA code


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

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *