Lock a Cell after Data Entry using VBA Excel with Message Box Notification before Locking

This video demonstrates how to lock a cell after data entry using Excel VBA. A message box is displayed before the cell locks providing the user an opportunity to change the value entered.

[nileshtikekar]

Thank you very much Dr. Todd Grande. It's very useful. And the way you teach is very good. Once again thank you.

[Singh-hum]

Thanks a lot for simplifying the thing..It really very helpful for me.. i got what i looking for...Thanks again.. Hope some more videos on small topics you will be uploading in future...Thumbs up...

[markarvin]

This is just what I have been looking for. Thank you!

[kgv]

Hi Grande, Now it is working fine when I am enable the macro to the file. it is working fine, thanks a lot.

[matq]

Is it possible to specify a range of cells to apply this program to? I.e. I only want Column A to have a entry confirmation and lock. what would need to be changed to this code to apply that? Excellent video, by the way! It was simple, precise, and easy to understand! 5/5 stars

[anemandanna]

Thanks Dr Todd for the tutorial.
I want to apply auto protecting/locking feature when user is entering the data in a specific cell range while the user is still able to change the data outside the specific range in the same sheet. (for example, user cannot modify the data once he enters data in the range from "IP21:ABK2500", but the user still can able to change the data outside this range such as in columns A to AO)
I have modifeid your code as below, but it does not work selectively for my range.
Appreciate your help on this.




Private Sub Worksheet_Change(ByVal Target As Range)

Dim cl As Range
ActiveSheet.Unprotect Password:="1986"
Set cl = Intersect(Range("IP21, ABK2500"), Target)


For Each cl In Target
If cl.Value <> "" Then
check = MsgBox("is this entry correct? This entry cannot be edited after entering the value", vbYesNo, "Cell Lock Notification")

If check = vbYes Then

cl.Locked = True

Else
cl.Value = ""
End If

End If

ActiveSheet.Protect Password:="1986", AllowFiltering:=True, AllowSorting:=True
Next cl


End Sub

[amitprakashgupta]

Sir, its really good ...working superb!! now could you please add the password in this code so anybody couldn't unprotect the cell from unprotect sheet tab ...!!Thanx in advance.

[igumguitara]

Thank u for the lesson sir..
Would you help me sir.. I have a little question still in this same case. How to make this work when we click yes so it will be used in a couple cell... for example i have 4 entry data long, so if i used your code the notification will be show up in each cell to corrected... thank you sir.

[yvonnehuang]

Sorry for this silly question.  If I am looking to only lock certain cells (not in consecutive row or column), what would the code look like?  Thank you.

[laurendean]

after hours and hours I think I have finally found my answer

[unclephoto]

Thanks Dr. Grande, it is possible to please do the same video for google sheets?

[primetimesstill]

I dont understand why when i close the file, the macros and protections disappear.

[kgv]

I try to use the same formula for my debtors agewise analysis statement but it is not working, please help me

[Mr.WS]

So when I type in data to cell, I click out the cell, message box pops up, I click yes, then my issue arises...says error 1004 unable to set locked property of range class. Wont lock the data put in the cell. Same thing pops up if I click no on are you sure box.

[hemantgoyal]

Sir how to filter participants and add data

[apache]

I am unable to share this excel VBA file on SharePoint for Multiple users can update their details
Please help....!!!

[oluseyiotukoya]

hi How would i lock the entire row after entering data in a specific cell on that row