Excel fails to unlock certain cells on a unprotected sheet

[JesperJ]

Facts I have this code that first unlocks, then changes the text of a cell, the colours are changed, in the end the cell is locked again.

For every single cel, my code first tests if the sheet is protected or not, if so, the sheet in question is unprotected. Next I test if the relevant cell is locked, if so I unlock it, if not I don't unlock.

So when the sheet s unprotected and the cell is unlocked I change the text and stuff, and then I lock the cell.

What makes me climb the walls is that this code repeats the process some 700 times, but 4 times it fails to unlock the cell:

I get a 1004 - Unable to set the locked property of the range class.

However for every cell I'm at the same time able to unlock/lock the cells manually from the Excel menu bar, so the sheet must be in unprotect mode. Together with my coleagues I've spent countles hours trying to find any clue however small to why theese 4 cells fail... The firsT 3 cells are D35-D37 , then another 50 cells are processed without any problem whatsoever, then all of a sudden the 4'th error occurs, after this 4'th eror another 600 ! cells are processed without any errors and the code runs fine !!!!!!!!!!

We've even seen that the number of error vary fom 1-4 depending on which PC I use, regardless of the fact that both machine uses Excel 2000, and runs the exactly same version !!!!!!!!!!!!!!

There are 3 text fields and a drop down validatoin box. But every time it's thees 4 cells...

Public Sub Lock_cell()

If ThisWorkbook.Worksheets("" & MatrixText(1, intY) & "").Range("" & MatrixText(2, intY) & "").Locked = True Then

ThisWorkbook.Worksheets("" & MatrixText(1, intY) & "").Range("" & MatrixText(2, intY) & "").Select

Call TestSheetProtection
ThisWorkbook.Worksheets("" & MatrixText(1, intY) & "").Activate

ThisWorkbook.Worksheets("" & MatrixText(1, intY) & "").Range("" & MatrixText(2, intY) & "").Select
ThisWorkbook.Worksheets("" & MatrixText(1, intY) & "").Range("" & MatrixText(2, intY) & "").Locked = False
End If

End Sub


Public Sub TestSheetProtection()

If ThisWorkbook.Worksheets("" & MatrixText(1, intY) & "").ProtectContents = True Then
ThisWorkbook.Worksheets("" & MatrixText(1, intY) & "").unprotect nlppassword
End If

End Sub

 

[Joe Was]

Re: Excel fails to unlock certain cells on a unprotected she

Your unlock code is running to do something, so why do you want to select what is unprotected?

Modify your code to unlock the wehole workbook, do your code then lock the workbook again when done. Don't use the "Worksheets("" & MatrixText(1, intY) & "").Range("" & MatrixText(2, intY) & "")" part at all when unprotecting!

 

[JesperJ]

Re: Excel fails to unlock certain cells on a unprotected she

Thanks for the input, but Ineed to control 1 cell at a time:

1- I Unprotect, if protected

2- I unlock if locked.

3 - I change the contents of the cell, I change the colour

4 - I lock the cell again

5- repaet the process some 700 times, after some 50 times the **** suddenly crashes 3 times thn works fne for anothr 150 celle, it crashes 1 time, and then it runs buitifully ever after ((