How To Lock Or Protect Cells After Data Entry Or Input In Excel
Articles Blog

How To Lock Or Protect Cells After Data Entry Or Input In Excel

January 9, 2020

hello everyone welcome to excel 10
tutorial in this tutorial I will show you how to lock or protect cell after
data entry or input in excel this is an advanced Excel tutorial and I will be
using excel vba for this tutorial remember I am using Microsoft Excel 2010
for this demonstration take a look at here this is the workbook I am working
on and I want to lock this cell right after I enter any data that means when I
enter a data and click back to edit it it shouldn’t work ok so to do that first
select the data range and right-click and click format cell click on
protection you can take a look here it’s locked by default Excel lock everything
and you need to unlock it from here okay just click here and this is unlocked click
ok now click on the review and select this
protect workbook or select protect sheet ok so now I am going to add a password
which is one two three four and again one two three four okay so now I just
locked this range with the password one two three four and now I’m going to use
a VBA to allow me to enter any data so take a look at here is it won’t work now
and select the range new code and I have already written the code here you just
use it and I will pin this code in the comment section make sure you copy it
from there so this is the code and this is the data
range which is a1 to b 15 and this is the a1 and b 15 so this is our data
range and this is the password which is one two three four just click save okay now click any cell and enter a data
okay and now click to edit it see new dialog
box will open and you will see the error so you cannot enter again or you cannot
edit this data that’s what I wanted to show you see I can enter any data but if
I try to edit it I cannot so this is how you lock or protect cell after data
entry okay this is what I wanted to show it’s really easy and it is really
helpful hope you like it thanks for watching if this video was helpful give
it a thumbs up if you need any help with Microsoft Excel you can ask me in the
channel discussion section share this video with your friends and please do
subscribe it means a lot to me this is Kazi signing out from Excel 10
tutorial thank you

Only registered users can comment.

  1. Did you subscribe? Here is the code:
    If you can please Support Us:

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Excel 10 Tutorial

    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A1:b15"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="1234"
    xRg.Locked = True
    Target.Worksheet.Protect Password:="1234"
    End Sub

  2. Hi Sir, could you help me in this regard that I have got 10 type of information vertically I want to record horizontally one by one but I tried many times it is record 3 lines only I followed your instructions videos. Even though it's not work well
    And I want while entering data serial number and date should enter automatically starting from left this is used to it
    Please help me in this regard thanks
    [email protected]
    I asked ones you before you didn't answer my question

  3. Please can you give me a code in which I can format cells (change cell colors) along with this one time editing and locking of cells. Because when I run this macro, cell color fill option gets disabled, I tried enabling it under the reviews tab>protect sheet, but it works only once and the the macro disables it again. I need a command in the macro which will enable formatting of the selected cells in the macro. Thanks.

  4. Hi Sir, I need help badly. Do u have email address where i can chat about my problem regarding Lock & protect cell after data entry or input in excel. Currently i have done and it works. But unfortunate i am able to access other cell which contains formula.. this cells were lock but when i run the program it is automatically.

  5. hi, thank you very much for this video and it works good but:
    cell not lock if we transfer a formula from other sheet
    any solution please ?

  6. The person who knows the password can he change it. if it is they its works for me. waiting for the reply.

  7. Simple great 👍 work…I think u are so much intelligent…please upload ur new video…I am ur big fan..

  8. Great tutorial just wondering can you tell me what the vba code would be to add into your code to change the error message that pops up when you try to change the data in a cell?

Leave a Reply

Your email address will not be published. Required fields are marked *