Pages

Add Data to a Protected Sheet Automatically Using VBA

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.


Data entry form

Once users enter the data through the form, we can save it to a well organized excel sheet like this.

Excel database

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.

Error message

And if you click the Debug button, a line will be highlighted.

One line 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 WS_Data As Worksheet
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"