How to protect certain cells so user can not change them.OnlyWant VBA code to make change.

See enclosed file.

I have this file where if a user makes a change to the cell entries in the first column then values in same row for cols C and D are cleared out. I dont want user to be able to input values into cells in column C and D. I only want VBA code to be able to make changes to cells in cols Cd and D. Not very familiar with protecting, locking cells.........is there anything i need to be concerned about?

royuk,
how do you do this? i selected the cells to lock, then went to the home tab then the format section and under this selected the lock option.......but i can still change the cells entries......must not be donig something right.

is this what i need to do:
Select entire sheet. Then "unlock" all cells. Then select only those cells i wish to protect. Then lock those cells. Then protect the sheet such that theuser can ONLY select those cells which are not locked? Is this how to do it?

is there any concern with doing this and then updating entries / cells via VBA code?

In the worksheet activation event, put this code:

Please Login or Register  to view this content.

The thing about allowing VBA to change locked cells is you need to tell it the instruction again every time you open the workbook, it kinda forgets. Therefore, if you protect the sheet automatically every time you activate the sheet then it'll always be protected but VBA will be able to make changes.

great info thanks! So maybe i dont want to go the protect cells route....maybe the cell validation route might be better.

swoop99, i did have one question about what you wrote ....first thanks for the info.......i am not 100% sure i understand what you were saying about excel forgetting.....do i have to protect the sheet each time the workbook opens?

thanks again........this is exactly what i am looking for.......thanks.

john55, how did you do this? do you think this way is better than the validation way......again thanks for the tips this helps out ALOT!

I think i posted something in a similar post that i should have posted here so...i will post it here now.

I have been working on my proj now for a little bit when i have time and this is what i have found.
I first plan to copy the 4 cells from a worksheet into my data. Then create a new sheet, which will copy the event code to this new sheet, then copy over my data (which includes the 4 cols) to this new sheet.

What i found is that when copying just the 4 cols to my dataset that the validation example allowed me to just copy the cells over and the validation copied with it. The "cell protection" did not........so if i want to use the cell protection method i have to figure out a way in vba to protect the cells i wanted to protect........

is there a way to protect cells like i want using vba? if not thats ok i will just use the validation example.

fotis1991,

how to manually undo the validation you put in your example file. I cant seem to "turn-it-off" manually.

fortis1191, i figured it out. i just cleared all.

i assume there is no way to password protect the validated cells criteria?

Ok, i am still working on this macro which involves protecting certain cells. I decided to "try" the protect cells method instead of the "validating" method. Perhaps i made a mistake but time will tell.

Anyway, here is the code i went with to protect certain cells.

Please Login or Register  to view this content.

The problem i am having is that now i get messages that says sheets are protected. I think someone in this thread was trying to warn me about this but i did not understand at the time.....well i still dont understand thats why i am having problems and cant figure out what to do.


Any ideas?

Also, what really confuses me is that i locked/protected "i thought" only certain cells on a given sheet. But the code is acting like i protected the entire sheet. And of course if i unprotect the entire sheet then i "UNDO" the protection of the cells i really want to protect........