Protecting Excel sheets is a great way to secure the entered data. This can avoid users accidentally changing the data. But if you create advanced data entry applications in Excel, using forms then you will need to know how to enter data to password protected sheets automatically. Because sometimes we protect the sheets and let the users enter the data only through forms. For example assume we have a form like this.
Once users enter the data through the form, we can save it to a well organized excel sheet like this.
Learn how to transfer data from a form to an excel sheet
Then to secure the entered data, we can protect the data sheet with a password. But once the sheet is protected, we can not use the technique explained in the above lesson to transfer data from a form to an Excel sheet. Then Excel will display a message like this.
And if you click the Debug button, a line will be highlighted.
Code has thrown the error when it tries to write data to the sheet. Because data can not be entered to a protected sheet. So how do we overcome this? We can use a simple trick to accomplish this. We can tell the program to unprotect the sheet before writing the data to the sheet. Then after writing the data, program can protect the sheet again to safeguard the data. Here is an example.
Dim Lastrow As Long
Set WS_Data = Worksheets("Data")
Lastrow = WS_Data.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
'Unprotect sheet
WS_Data.Unprotect Password:="password_here"
Enter data to Data sheet
WS_Data.Range("A" & Lastrow + 1) = txtOrderNumber.Value
WS_Data.Range("B" & Lastrow + 1) = txtSize.Value
WS_Data.Range("C" & Lastrow + 1) = txtSerialNumber.Value
WS_Data.Range("D" & Lastrow + 1) = txtProjectNumber.Value
WS_Data.Range("E" & Lastrow + 1) = txtType.Value
WS_Data.Range("F" & Lastrow + 1) = txtDate.Value
WS_Data.Range("G" & Lastrow + 1) = txtSurveyor.Value
'Protect sheet
WS_Data.Protect Password:="password_here"
