Скачать или смотреть Lock Cells After Userform Input in Excel Using VBA

Discover how to `lock cells` in Excel after a userform input. Learn step-by-step how to protect your data effectively using VBA.
---
This video is based on the question https://stackoverflow.com/q/62410507/ asked by the user 'Dino Blažević' ( https://stackoverflow.com/u/8696549/ ) and on the answer https://stackoverflow.com/a/62410892/ provided by the user 'Scott Holtzman' ( https://stackoverflow.com/u/1569536/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Lock cells after userform input

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Lock Cells After Userform Input in Excel Using VBA: A Complete Guide

If you're using Excel and have created a userform for data entry, you might have encountered the need to lock certain cells after the userform is submitted. This ensures that once data is entered, it cannot be changed or edited, safeguarding the integrity of your data. In this post, we will walk through how to lock specific cells in your worksheet after a userform submission using VBA.

The Problem: Locking User Input

Imagine you have a worksheet (let’s call it "Table") where users input their data via a userform. You want to ensure that once a user submits the form, the specific row of data entered is locked from any further editing. This will help maintain accurate records while allowing submission through the userform.

Here are the requirements for your solution:

Rows from A4 to AF4 and onwards need to be locked.

The submitted data should go to a worksheet named "Details".

The Solution: Step-by-Step Instructions

To achieve the locking of cells after userform input, follow these detailed steps:

Step 1: Manually Lock Cells

Open your Excel workbook.

Navigate to the "Table" worksheet.

Select the range you want to lock (from A4 to AF and the last row you desire).

Right-click and choose Format Cells.

Under the Protection tab, check the box for Locked.

Protect the worksheet:

Go to the Review tab on the ribbon.

Click on Protect Sheet, enter a password, and ensure that the option to select locked cells is unchecked.

Step 2: VBA Code for Data Submission

Next, you will need to write the VBA code that handles the submission of data through your userform. Here’s how to set it up:

Open the VBA Editor:

Press ALT + F11 to open the VBA editor within Excel.

Create Your Command Button Code:

Locate the userform where your command button is placed, and input the following code:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

The code begins by defining the worksheet "Details" where the data will be stored.

The .Unprotect method is called to allow editing during the data entry.

The lastRow variable calculates the next empty row based on existing entries.

The values from your userform (TextBox3, TextBox4, TextBox5) are assigned to the respective cells in the identified last row.

Finally, the worksheet is re-protected using the .Protect method.

Final Setup

Ensure all your form controls are named correctly and correspond to the code.

Test the userform by entering data and checking if the row locks properly afterward.

Conclusion

With these steps, you can effectively lock inputted data in your Excel worksheets after submission through userforms. This not only enhances the functionality of your workbook but also keeps your data secure and unaltered. Feel free to customize the code further as per your specific requirements!

If you have any questions or need further assistance with VBA in Excel, feel free to leave a comment below.