Auto Lock Cells After Data Entry

[DixieLou]

Test Doc.xlsm

I have read thru several threads regarding using a VBA code to automatically lock a cell after data has been entered. I have not had any success with copying the code provided into my spreadsheet and making it work so I am posting in the hopes that I can get some help. I am not familiar with using VBA codes so please write your response in layman's terms.

At my job we are trying to stop printing out so many documents and are starting to change over to more documents being completed electronically. Every document will have different fields that need to be completed and most will have multiple users. Like other people that have posted this question, I need to prevent User B from changing User A's input. I have attached a sample document that we use and that I have tried to apply a VBA code to.

My assumption is that I start by unlocking the fields that need to be completed & then protecting the sheet. From what I have read, after that the VBA code would take over & lock the cells as data is entered. If this is correct, I have not been able to get it to work.

Thanks in advance for the assistance

[AlphaFrog]

Hi DixieLou from Wisconsin. Welcome to the forum.

It looks like you were 99% there. Your original code worked as is except on merged cells which you have in the Inspector column.

The code below will work on merged and not-merged cells. Also, in your case, the two lines to EnableEvents are not needed as the code doesn't change a cell's value. And I replaced the IF statement, but that was more a matter of programing style than of substance or function.

Please Login or Register  to view this content.

[LJMetzger]

Deleted - AlphaFrog's answer much superior.

[DixieLou]

Excellent! Thank you so much - this code worked!
I did figure out that I need to get my worksheet set up first and then put in the VBA code. It didn't work if there was already data entered and then put in the code.

Next time I have an Excel question I will come here first instead of spending loads of time trying to figure it out on my own.

[AlphaFrog]

Excellent! Thank you so much - this code worked!
I did figure out that I need to get my worksheet set up first and then put in the VBA code. It didn't work if there was already data entered and then put in the code.

Next time I have an Excel question I will come here first instead of spending loads of time trying to figure it out on my own.

You're welcome.

Spending loads of time trying to figure it out was probably a good learning experience. You were really close to creating a final solution on your own. FYI: merged cells are a pain to deal with in VBA. I avoid using them as much as possible.

[Vdogeek]

Is that code specific to the person whom asked the question or can i use that same code in my sheet?

[AlphaFrog]

Is that code specific to the person whom asked the question or can i use that same code in my sheet?

Both.

Its not exclusive to anyone. Though I have no idea if it works for your sheet.

[Vdogeek]

Tried it, no go. I'll play around with it some more. Thanks