Excel fails to unlock certain cells on a unprotected sheet

JesperJ

New Member
Joined
Jan 19, 2005
Messages
2
:devilish:

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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

New Member
Joined
Jan 19, 2005
Messages
2
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 :(((
 

Forum statistics

Threads
1,147,688
Messages
5,742,627
Members
423,744
Latest member
bkirtland

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top