Run-time error '1004' Unable to set the Locked Property of the Range Class

I keep getting the following error message:

Run-time error '1004' Unable to set the Locked Property of the Range Class

I want to unlock the cell if the checkbox is checked and when it is not I want to set the cell to a formula and lock it.

Please help

Please Login or Register  to view this content.

Thank you

You realise you are referencing two different sheets there? Sheet1 and Check_Box - was this intended?

Thank you very much for your input. I copied abousetta's code and I am still getting the run time error. Do you have any other ideas?

Thank you for your help. I am getting a couple of things. When the error pops up and I click "Debug" the highlighted code is

Please Login or Register  to view this content.

Also the SpigDiam function is not working.It will highlight BB5 and say "Compile error: ByRef argument type mismatch"

The code I am using is below:

Please Login or Register  to view this content.

I am getting the error on the line ".Range("BB9").Locked =True"

The SpigDia function is working. I know this because if I unlock Cell "BB9" before I run the code it works fine (except for locking or unlocking the cell). But when I start with the cell locked I get the error.

Thank you for your help.

Could you please upload a sample WorkBook of your problem, so that we can take the guessing out of possible solutions.

Here is the solution. I am a beginner with VBA and far far far from being a Guru. But I had the same problem and spend 2 days over the internet to find the solution. It has something to do with Merged Cells (Excel which is a product of Microsoft - a corporation who does not give a ***** about its clients) did not bother to tell anybody about the merged cell problem.
Well I used to have
.Range("D4").Locked = True Which gave me a 1004 error continuosly
Now I changed it to
.Range("D4").MergeArea.Locked = True and it is working !!!!!

When I was a programmer Analyst with IBM MainFrame, front end CICS, Cobol. It might require you to code 5 times more lines than maybe VBA , but everything was clear, known in advance, with no surprizes. VBA , Csharp and all those new languages, require a huge level of expertize, code is criptic , and even the author can not understand what he wrote later on, let alone somebody else. From a community of 100% Excel VBA programmers , Just very few can code REAL VBA functional code. The code realisticly CAN NOT be maintained later on by somebody else. If you are realistic, on the Mai9nframe it will take you 1/2 of the time to develop an application. Yes I am frustrated, I managed to code wonder code in VBA Excel in just 3 weeks, from scratch. But to become a guru like yourself, it will take me years... Thank you, (no need to reply unless you enjoy the conversation...).

I studies before C# (C sharp) and C and C++, they are all the same, somewhat better then VBA (C# through Visual Studio) - because I wanted to beat the stock market. After the end of the project (which was a failure anyway, because you can not beat the sharks on wallstreet, I discovered that I could have done the same very thing with the antiquated Cobol2 (which has function capabilities) it would have taken me 5 times more code with Cobol but the debbuging would be a piece of cake. (IBM IS NOT repeat is not MicroSoft) And if you look at the code 10 years later, you WILL UNDERSTAND what you wrote. In the early eighties, there was an operating system on PC by IBM, it was called DB2pc or something like that, DOS operating systems with point and click which Microsoft lounched was Full of bugs, but it was "nice" click and point, so the world is left with all the garbage now. Ofcource people will tell you "do not listen to this expired dinosour old man (me). I have heard many talks about Object oriented code, in theory maybe, but try to ask a Guru what is object oriented code? They do not know themselves. Anyway, happy ending, I am a landloard now, and out of the USA (which btw, is going down the tube). With those "encouraging" words, I wish you a good night from Europe, and thanks for the chit chat.