Lock a Cell for Editing IMMEDIATELY After Changing it - Excel VBA Is Fun - Speedtip!

Apinan wanted to have the user edit a cell, and as soon as they exit the cell or hit enter, the cell would be locked and no longer editable. Here's the quick solution we found to help him. Try it out!
*Limited Offer* Learn to Make Excel Do Your Work For You with The Ultimate Excel Programmer Course - Get the Full 9+ Hour Premium Course for 75% off using coupon code: www.udemy.com/... Click Now to Order and get Lifetime Access to Course, Workbooks, Updates and Support!
------------------------
Create Your Own Barcode Lookup System Using Excel VBA. Learn to Make Your Own Barcode Labels the Easy way and have fun with Barcode Scanners to Automate your Workflow! In this project-driven Course, you’ll learn to Build your own Custom Inventory System with Step-By-Step video instructions. This goes in depth into some advanced Userform strategies that you can use for your other projects to come. A great weekend study! KZbin Subscibers only pay one forth! Get your 75% off Coupon here: www.udemy.com/...
------------------------
Too busy to Learn Piano? Use my Secrets and Play Your Favorite Songs Today! Did you know; You can learn 4 basic chords in about 5 minutes and instantly be able to play millions of songs. Seriously, check this course out. 75% off for my KZbin Friends! Click here: www.udemy.com/...
For Articles, Updates and to Contact me for Consulting or just to drop me a line, please visit www.ExcelVbaIsF...
To download this FREE workbook(source code included): Click the link below.
excelvbaisfun.c...
------------------------
Playlists:
Excel Vba Basics • Excel VBA Basics #1 - ...
Tips N Tricks • Excel VBA Tips n Trick...
Userforms • UserForm Series
Sample Excel Programs • Sample Programs
Events Series • Worksheet Event Series
Activex Controls • ActiveX Controls Series
------------------------
Grab an Excel Nerd T-Shirt or Hoodie:
ExcelVbaIsFun.s...
------------------------
Interact and Follow me on:
LinkedIn: / danielcstrong
Facebook: / excelvbaisfun
Twitter: / excelvbaisfun
Google Plus+: www.google.com/...
'Royalty Free Music by audiomicro.com/...

[@kabelokelepile7231]

Perfect -I found this very vital to my work.

[@Juchrisad]

Very straight forward and simple to follow - Thanks

[@ExcelVbaIsFun]

Thanks Juchrisad!

[@jpacello]

Is it possible to only have certain cells auto lock and be unchangeable? I have only about 10 cells that I would like to lock after data is entered. All the other cells can be freely edited over and over. Is this possible?

[@tariq912ahmed]

Please let me know if you found this option

[@jaredmajors-manley7712]

I found this video extremely helpful. But I need to lock a range of cells only if the user changes a value and saves the workbook? Any ideas?

[@hassanzahid1055]

Hi Dan, Your video was very helpful in making me understand how to lock cells right after input. However would you be able to help if i want to lock the cells after the files have been saved. I have no indepth knowledge of excel please help.thanks

[@CanadaSocial]

Thanks a lot for this. How can I get grouping to work on locked cells and protected worksheet? I want to share a sheet that is protected but allow the end user to use the grouping toggle to hide or unhide columns. Also can you advise me on how I can lock a group of cells in a column based on the value of a table header that has future or past

[@excelisfun]

Thanks, excelvbaisfun!!!

[@christalrobison6804]

Very helpful, but I was wondering if it was still possible to get a copy of the Essential Excel VBA Snippets book you talked about in this video? There was no link to click on as described.

[@yehoshuvaandco3842]

what about the dropdown menu, and changing the colors as per the options available in the drop down menu in the above protected vba mode.. Thank u

[@laurendean9920]

is there a fast way to add this programming to the workbook or do you have to protect each worksheet

[@emanalbar]

Thank you so much. I learned a lot from you.

[@jonpitt7793]

Hi Daniel, thanks for your great tips and just wondering if you can solve this one. How do I ensure password protected sheets with unlocked cells can't be broken by coding provided on the web? I only ask as I have set up a spreadsheet with unlocked cells for others to populate, password protected the sheet to avoid others from altering specific data, however they have used coding from the web to break the password protection. Please provide a solution video that shows how to password protect an excel doc that can't be broken by any coding if possible. Thanks in advance.

[@nlch891]

Luv your channel..been very helpful

[@johnmagombo3045]

Thanks a lot for this. This is helpful

[@sandeshnathani11992]

Thank u sooo much! Very useful tool

[@ExcelVbaIsFun]

so welcome!

[@JohnPauljackulinbaby]

hi It is working nicely, thanks a lot

[@ripvista]

Is it possible to auto protect a cell range i.e (c4:c9) plus a separate cell i.e (j29) after data entry and have a message box display only for cells in range and J29 with a yes no response (vbYesNo) to verify data, where yes locks cell and no lets user re-enter data. Only these cells. The rest of the sheet has formulas and locked/unlocked cells

[@jashonce]

Hello, is it possible to have a limit of 3 cells to be edited in a column? The Cells in the Column will no longer be editable if there is a SUM of 3 in a Range/Column. The Range/Column will be editable again if the user reduces the number of the edited cells on the range/column. I am confident that you can help Thank you very much!

[@justdoit54321]

fantastic! thanks for sharing!

[@PurushothamanB]

Thank you so much!!!Lovely

[@lycanthropyyyyyyyyyy]

How do you protect 3 different sheets(1wb) in different times

[@pepejunction2836]

How can I founded in my Excel-2007 option is "Developer" under VBA Projected. Pls advise me.

[@Declassed]

Thank You! How did you learn that kind of coding in excel?

[@Declassed]

Subbed!

[@omarfuentesherrera1980]

Hi can you help me with a code, I want to insert data from a userform in VBA-Excel and after the insertion of the data I want to lock the worksheet where I am inserting the data, can you help me with this? thanks man

[@harshsurana5958]

Hi, Can you please help me achieve the same thing in Google Sheet ? Thank You

[@mykitchen3355]

Sir, may i know how to auto lock cell after enter data in google sheets ?

[@bakajatta5672]

thanks

[@andrejhorvat378]

Thanks!

[@ExcelVbaIsFun]

Welcome!

[@syedabbas2884]

i have an excel sheet for data center visitor log in which i want to run 2 codes in vb which enters date and time automatically in date and tiem column and then protect the column so that it cant be changed or edited after entering data in all the columns.

[@ExcelVbaIsFun]

Hi Syed, Do you have a sample layout I can look at? Please send me a dropbox/google drive link or use my contact form at excelvbaisfun.com/#contactus Thanks Dan

[@marksh5507]

i want code to don"t change one textbox onley can change any textbox except one in userform thanks

[@dritannaum4360]

Very good way to lock the cell, I'm trying to download but is not working (even that I have a dropbox)?

[@ExcelVbaIsFun]

Dritan Naum www.dropbox.com/s/nhnlkdf1xu9m8hw/Lock%20Cells%20after%20Changing%20them.xlsm?dl=0 is the direct link, Dritan.

[@SantoshLoka]

i have a issue with cell can u please solve as soon as possible ,iam trying to hide formula of cell..in my program user can edit few cell for input i locked output,but they can see my formula of output cell

[@naresh90sharma]

Sir same code using but all cell is located can't be edited any cell

[@suryaflex1743]

good

[@aamir6282]

Sir if i want to edit it again then how can i do this

[@rameshvazhipokkil1580]

what about merged cells? debuug error!!!

[@TheAlexanderEdwards]

Presently, this script locks the cell after any interaction whatsoever. So let's say I accidentally enter a cell, but I don't add any data. This script will lock that unmodified cell. I'd like to lock it only if data is added. Is this possible or no?

[@ExcelVbaIsFun]

make sure you put it on the cell change event, rather than the cell Selection change event. they look very similar. Thanks Dan

[@TheAlexanderEdwards]

+ExcelVbaIsFun Still doesn't do the trick. I even went to your test worksheet and the behavior is the same. Once you interact with a cell, regardless of change, it will lock said cell. Any ideas?

[@ExcelVbaIsFun]

+Alex Edwards Hi Alex, try debugging through each line by placing the word STOP at the beginning of the code, then hit F8 to go through line by line. You can then identify the offending line of code or when the code is being triggered and when it isn't. Thanks Dan

[@TheAlexanderEdwards]

+ExcelVbaIsFun It's not that the code is buggy. In fact, the syntax is 100% correct. It's the fact that this will lock any cell that is interacted with, period. Even if no changes were made to the cell. I'm looking to write a script that will lock the cell IF and ONLY IF the user enters data. So, if the user enters the cell, and then decides, "Nah, I don't want to put anything in here just yet", the user will not be locked out of the cell. Thanks for your help Dan!

[@cywang3988]

can anyone help me, after i uploaded the excel online, it does not work anymore @ ExcelVbaIsFun

[@dpc0809]

Elegant

[@dritannaum4360]

I repeated your action in a worksheet, but it lock all the cells any help?

[@ExcelVbaIsFun]

Dritan Naum Did you unlock an area to test this on first, You must first unlock some cell. By default they're all locked.

[@dritannaum4360]

ExcelVbaIsFun I unlocked and worked perfect. Thank you once again

[@ExcelVbaIsFun]

Dritan Naum very welcome!

[@Berghiker]

How can I lock just one cell WITHOUT locking the whole sheet??

[@tariq912ahmed]

Have you found this option

[@Berghiker]

@@tariq912ahmed Yes.Thanks.

[@pirqayomazizi9148]

My question is; in protected sheet how we can insert row or column? Any body can help us?

[@ExcelVbaIsFun]

Hi pirqayom, I answered your question in my latest video. Thanks for the good question! kzbin.info/www/bejne/a2SZi2lvot-fjLM Thanks Dan

[@deepakshankarchaugule7417]

How to create a sheet after a day data is automatically lock

[@haqobatil9832]

sir i want to use my format which i defined only for cells i.e "a1111a1111"

[@dritannaum4360]

But How can I put a password so if any user want to re-edit the cell he must put the password?

[@debsjoe1]

Did not work with

[@VincentJanNicer]

i was able to do this but the user will just click "Unprotect sheet" then he can edit the entry once again. :( so it beats the purpose

[@glenokelly2670]

You confuse me I have seen other videos that’s similar but easy

[@ExcelVbaIsFun]

Sorry about that! Glad you enjoyed the other videos. Have an amazing day! :)

[@TomaszSmykowski]

Thanks!