How To Lock Cells After Data Entry In Excel

Excel Tips
Excel Functions
Excel Formulas
Excel Charts
Word Tips
Outlook Tips

How to lock or protect cells after information entry or input in Excel?

Supposing you take a worksheet and just a sure range of blank cells needs information entry, and after finishing inputting information, you lot demand the cells to exist locked automatically in order to prevent changes again. How can you do to attain it? This article can help you.

Lock or protect cells after data entry or input with VBA code

Lock or protect cells after information entry or input with VBA lawmaking

For instance, the certain range of blank cells is A1:F8. Delight practice as follows to lock these cells after data entry in Excel.

i. Please unlock this range starting time, select the cells and right-clicking, then choose Format Cells in the right-clicking carte du jour, and in the Format Cells dialog box, unchecking the Locked box under the protection tab, and finally clicking the OK push button. Meet screenshot:

two. Click Review > Protect Canvass. And specify a countersign to protect this worksheet.

3. Right click the sheet tab, select View Code from the right-clicking menu. Then re-create and paste the below VBA code into the Code window. See screenshot:

VBA code: Lock or protect cells afterward data entry or input

                Dim mRg As Range Dim mStr As Cord  Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel Equally Boolean) If Not Intersect(Range("A1:F8"), Target) Is Nothing And so     Set mRg = Target.Item(i)     mStr = mRg.Value End If End Sub Private Sub Worksheet_Change(ByVal Target Every bit Range)     Dim xRg As Range     On Fault Resume Next     Set xRg = Intersect(Range("A1:F8"), Target)     If xRg Is Nothing And so Exit Sub     Target.Worksheet.Unprotect Countersign:="123"     If xRg.Value <> mStr Then xRg.Locked = True     Target.Worksheet.Protect Password:="123"  Stop Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Range("A1:F8"), Target) Is Zippo Then     Set mRg = Target.Item(one)      mStr = mRg.Value End If Finish Sub

Note: In the code, "A1:F8" is the range you need to input information; and "123" is the password of this protected worksheet. Please modify them equally you lot need.

4. Press Alt + Q keys simultaneously to shut the Microsoft Visual Basic for Applications window.

After finishing entering information to the cells of range A1:F8, they will be locked automatically. And you volition get a prompt dialog box if you try to change whatever cell content of this range. See screenshot:

How to lock all cell references in formulas at once in Excel?
How to lock or unlock cells based on values in another cell in Excel?
How to lock picture/image to or within cell in Excel?

The All-time Office Productivity Tools

Kutools for Excel Solves Nearly of Your Problems, and Increases Your Productivity past fourscore%

Reuse: Apace insert circuitous formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Information; Split Cells Content; Combine Duplicate Rows/Columns... Forestall Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Discover and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove past Position, Remove Infinite; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and use filter schemes to other sheets); Advanced Sort by calendar month/week/24-hour interval, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Carve up Data into Multiple Sheets; Batch Catechumen xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Piece of cake deploying in your enterprise or organisation. Total features 30-24-hour interval free trial. threescore-24-hour interval money dorsum guarantee.

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Give-and-take, Excel, PowerPoint , Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for y'all every day!

officetab bottom