Excel protection (Validated cells)

[klmorgan]

I have used a macro to protect certain rows in a sheet once a user has marked them as completed but I am having difficulty with the cells that have Validation set on them.

The macro takes the sheet protection off, locks the cells and puts the sheet protection back on.

Although the cells are protected againgst typed entry you can still change them by picking from the drop down list.

Any ideas on how I can prevent this?

Keith

 

[xlbo]

Interesting - if I protect the sheet, you can't enter data through Data Validation....what version of xl are you using ??

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[klmorgan]

Excel 2000

After I read your note I tried manually protecting the cells and protecting the sheet and this still prevents typing but allows change via the validation drop down box

Regards

Keith

 

[xlbo]

Gonna have to bow out here - working on XP and it doesn't allow selections from a list when the sheet is protected so I can't replicate the problem

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[xlbo]

Just had an idea tho - bit clunky but you could reference all the validation cells in one cell eg
=A1 & B10 & C12 etc etc
then use the calculate event (the change event is not fired from dropdowns) to check the ACTIVECELL.ADDRESS - you would need a list of validation cells to check against eg
For each c in sheets("CheckSht&quot.range("Validationcells&quot
if c.text = activecell.address then
'validation cell used
else
end if
next

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[EdwardA]

Hi - a few months after your original post, but found this after a quick search, gave up and then found a solution.

If you want to allow users to select entries via data validation list and then protect the sheet so that no further entries can be selected in the validated cells, then you need to remove the "In cell drop down" property of the validated cells.

This could be done via a macro which is called when the protection on the sheet is applied.

Cheers.