How to Lock a cell based on option picked from a data validation list

Hi, I am new to the forum. i have been asked to create a spreadsheet which contain many data validation list. Sports IV Plan Tracker.xlsx

I would like count 1, count 2 and count 3 to be locked when 'Completed' or 'not required' are selected from progression 1, progression 2 and progression 3. So, if F2 changes to 'completed' then G2 becomes locked and no longer continues to update the days pasted due date.

From searching the internet it is clear that VB script would need to be used, but i have no idea how to write the script or where to put it. Please help, it will be much appreaciated.

Thanks

Dina

Could you post a copy of your file witha detailed explanation of what you want to do. It would be easier to help if we can see the data.

Hi, the file is above 'Sports IV Plan Tracker. xlsx'. I need to lock a cell (count 1, count 2 and count3) when 'progression 1' from a data validation list shows as completed. So if 'progession 1' for F2 is changed to 'Completed', then G2 becomes locked. When 'progression 2' for J2 is changed to 'Completed', then K2 becomes locked and when 'progression 3' for N2 is changed to either ' Not required' or 'Completed', then O2 becomes locked.

I hope that helps.

Dina

Hi. Place the following code into the worksheet module for "Sheet1" (not into a regular module). I noticed that the validation list for Progression 3 has a typo in it. You should correct cell H6 in Sheet2 (change "nor" to "not"). To activate locking and unlocking of cells, you have to do a couple of things. First, unlock all of the used cells in Sheet1. Then you have to protect Sheet1. When you protect Sheet1, uncheck all the boxes except for "Select unlocked cells". The macro will automatically lock and unlock the cells in columns G, K and O depending on your choice in the drop-down list. You can also protect the sheet with a password if you wish but if you do, the code has to be modified. If you want to use a password and are unsure of how to do that, please let me know.

Please Login or Register  to view this content.

Hi Mumps1

Thank you for your quick reply. I changed 'nor' to 'not'. Thank you for noticing the typo. When you say the code needs to be added to the worksheet module for 'Sheet 1', where would this be? Sorry, this is the first time I have tried to do something like this.

Thank you again for your help. It's much appreciated.

regards

Dina

Hi Dina. Right click the tab for Sheet1 and then click 'View Code'. Copy and paste the code into the window that opens up and then close the window to return to your sheet. Let me know how it works out.

Hi Mumps1

I have done what you said and it all loks like it is working, but will be better able to test it tomorrow to see if the test data shows that the cells have locked. Thank again for all your help and patience. I will let you know if all has worked tomorrow.

regards

Dina

Hi Mumps1

I do not think I have done what you said correctly, as the 'count' field keeps on changing each day, even though the status has been changed to 'completed' or 'not required'. I am attaching another copy with the changes, hoping you could have a look to see what I have dome wrong and how to fix it.

Thanks for all your help.

regards

Dina

Sports IV Plan Tracker.xlsm

Hi Mumps1

I do not think I have done what you said correctly, as the 'count' field keeps on changing each day, even though the status has been changed to 'completed' or 'not required'. I am attaching another copy with the changes, hoping you could have a look to see what I have dome wrong and how to fix it.

Thanks for all your help.

regards

Dina

Attachment 210963

Hi Dina. I tried your file and it worked properly, that is, if you choose "Completed" or "Not required", the adjacent cell is locked. If you change or delete "Completed" or "Not required", the cell is unlocked. In order for 'locking' and 'unlocking' to work, make sure that the sheet is protected. The 'Count' field changes each day because the formula you have in those columns subtracts today's date from the date entered in the cell two columns to the left. Therefore, you will see the 'Count" field change by 1 each day.

Hi Mimps1

ok, thanks for explaining and testing it for me.

regards

Dina

Not a problem!