Changing locked cells on a protected worksheet

Posted by David on December 22, 2001 7:32 PM

Hello:
I am working on a spreadsheet to store and print a daily production report (this will later be intergrated with Access97). My problem is how do change the locked property of a group of cells to allow data entry only if the cell is blank? I tried the following code to unlock all the cells, but I get a run time error that says it is unable to set the locked property of the range class.

Sheets("Operators").Select
Worksheets("Operators").Activate
Worksheets("Operators").Range("A2:A22").Locked = False
Worksheets("Operators").Protect

what would be the best way to have the code check the cell and unlock only if the cell value is null?

Thanks in advance

Dave

Posted by Ivan F Moala on December 23, 2001 2:03 AM

Dave

unlock the sheet through code before
running the code to do this as this error
will happen if you have already protected
the sheet......eg

Sheets("Operators").Select
Worksheets("Operators").Activate
Worksheets("Operators").Unprotect
Worksheets("Operators").Range("A2:A22").Locked = False
Worksheets("Operators").Protect

Ivan

Posted by Gary Bailey on December 23, 2001 5:13 AM

If you want to unlock all the blank cells on the whole worksheet (not just the used range) then try

Dim rngCell As Range

ActiveSheet.Cells.Locked = False

For Each rngCell In ActiveSheet.UsedRange.Cells
If rngCell.Value &LT;&GT; "" Then
rngCell.Locked = True
End If
Next rngCell

Which should leave you with all the blank cells unlocked and the nonblanks locked.

Gary

Posted by David on December 23, 2001 1:52 PM

Ivan:
I think I'm still missing something. I changed the code to:
Private Sub CommandButton3_Click()

Sheets("Operators").Select
Worksheets("Operators").Activate
Worksheets("Operators").Unprotect ("my password")
Worksheets("Operators").Range("A2:A22").Locked = False
Worksheets("Operators").Protect ("my password")

End Sub

I still get the run time error that says it is unable to set the locked property of the range class. Am I just slow or am I missing something?

Thanks and Happy Holidays

Dave

Posted by David on December 23, 2001 1:55 PM

Gary:
Is there anyway to select only the range using this code? Sorry, but someone keeps building better idiots so I have to try to stay ahead of production:)

Thanks and Happy Holidays

Dave

Posted by Ivan F Moala on December 23, 2001 7:34 PM

Excel97 ???

If so then change the buttons Takefocusonclick
to FALSE

Ivan Ivan:

Posted by David on December 24, 2001 10:40 PM

Yep...I must be slow........

Ivan:
Yes excel97
I change Takefocusonclick to false and still get the same error. Any other ideas?

Thanks and Happy Holidays

Dave

p.s. Sorry to be such a pain in the backside.......

Posted by Ivan F Moala on December 25, 2001 2:18 AM

Re: Yep...I must be slow........

Dave I'm @ a loss ???
If possible then could you email me the problem sheet.......

Ivan Ivan:

Posted by Dav id on January 09, 2002 10:37 AM

Did You get form through e-mail Ivan? (nt)