Transfer Data from a Data Entry Form to a Protected Worksheet

In this post, we are going to learn VBA codes to transfer data from a Data Entry form to a Protected worksheet.

Watch our YouTube Tutorial

Here, I have developed a data entry application to capture Visitor details.

If user will click on ‘Add Visitor’s Details’ button to open the UserForm and after updating the all the fields, user needs to click on ‘Submit’ button to transfer the data from ‘Visitor Entry Form’ to ‘Home’ sheet.

Here, there could be two possibilities. Either the worksheet is protected with password or not protected. If it’s not protected then we can easily transfer the data but in case, worksheet is protected then we can’t update the data without unprotecting it.

In this file, our ‘Home’ sheet is protected hence, we need to unprotect it before transferring the data and then after updating the data to home sheet, we need to protect it again so that user can’t make any changes.

So, let’s move to the VBA window. To open VBA, you need to click on Developer Tab and then click on VBA button available in code group.

Once you click on Visual Basic button or press ALT + 11 as shortcut key, it will open a separate window for Visual Basic for Application.

In this project, we have 2 forms and 1 module. First form is for data entry and second is for calendar control which we have developed. You can find a separate tutorial on this.

In module window, we have several Sub Procedures to perform all the required task. You see the below codes and brief explanation of all the procedures available in module window.

Code to Reset or Initialize the Visitor Data Entry Form

Function to Validate the Email-Id entered by User

Function to validate all the entries made by user in Visit Data Entry Form

Procedure to transfer the data from UserForm to worksheet

So, all the above functions/sub procedures are available in module window.

Let’s explore all the code available in frmDataEntry UserForm.

Click event procedure of Reset button

Click event procedure of Calendar image to open the calendar form

Double click event for Visit Date Text box to open the Calendar form

Code to reset the UserForm on Form Initialize event

In Home worksheet, we have only one sub procedure on click event of button. It will show the form.

Now, we have fair understanding of all the procedures and functions uses in our project. In this project, only one code is pending to transfer the data on click event of Submit button in UserForm

Let’s double click on Submit Button in UserForm. It will open the code window with cmdSubmit_Click procedure.

Please see the below code to get the confirmation from user and then unprotect the sheets and transfer the data and then protect it.

Now, we are done with the required code. You can visit to Home sheet and run the form to transfer the data to protected sheet.

Please download the demo file used in this post.

Support File

Thanks!