Protecting cells with data validation

Posted by Andy on January 26, 2001 2:01 AM

I have a spreadsheet which contains several columns with drop down boxes. The problem I have is protecting these cells. Even though the cells are locked and hidden (and the spreadsheet is protected) it is still possible to alter the contents of the cells with drop down boxes.

Any suggestions?

Posted by Azri on January 26, 2001 4:26 AM

What do you mean by that?
I've also doing validation
and when I protect the sheet,
no one can altered the validation
contents unless you did not
protect the cell(although) you
have already protect the sheet
Check it again

Posted by Andy on January 26, 2001 4:35 AM

The cell contents can be changed to another option within the drop down box, but I want to lock the cell so it can't be changed at all.

Posted by Azri on January 26, 2001 6:24 AM

Go to the cell that have list drop down
validation...Ctrl+1 and you'll go to format
click the tab properties and
check the locked box.close it
and lock your worksheet..HTH

Posted by Andy on January 26, 2001 6:33 AM

Tried that, but it doesn't work, the cells can still be altered.

Posted by Azri on January 26, 2001 6:40 AM

That pretty weird..I wonder why...
Feel free to email me...maybe I
can looked whats going wrong

Tried that, but it doesn't work, the cells can still be altered.

Posted by Tim Francis-Wright on January 26, 2001 7:05 AM

If the problem is only with cells with list
validation, then you can solve the problem
by setting InCellDropdown to false.

The following macros will do the trick.
You'll need to define a range (ValidatedRanges)
that has all of the cells with list validation.

Sub ProtectSheetForReal()
Dim ValidRange As Range
Dim cel As Range

Set ValidRange = Range("ValidatedRanges")
For Each cel In ValidRange
cel.Validation.InCellDropdown = False
Next
ActiveSheet.Protect
End Sub

Sub UnProtectSheetForReal()
Dim ValidRange As Range
Dim cel As Range

ActiveSheet.Unprotect
Set ValidRange = Range("ValidatedRanges")
For Each cel In ValidRange
cel.Validation.InCellDropdown = True
Next
End Sub

Hope this helps.