Lock / Unlock cells depending on True / False

[tlc53]

Hi there,

I'm not sure why this code isn't working. Can someone tell me if I'm missing a trick please?

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A17") = "TRUE" Then
Range("F17:K17").Locked = False
ElseIf Range("A17") = "FALSE" Then
Range("F17:K17").Locked = True
End If
End Sub

Thank you!

 

[KennyGreens]

Hi there, locking cells doesn't actually fully lock them as you'd expect unless you protect the sheet. You'd have to add in something like this:

Worksheets(1).Protect

 

[tlc53]

Hi. I appreciate what you are saying and I was going to deal with the protection side later.
I was however expecting my code to tick/untick the Protection box under Format Cells but it's not doing that. Are you saying it can't do this unless I deal with the protection side first?

 

[Fluff]

How is A17 being changed?
If being changed manually, or via data validation, remove the quotes from True & false.

 

[tlc53]

A17 is linked to a Check Box. If it's ticked A17 reads TRUE, if un-ticked FALSE.
I had tried removing the quotations but that didn't work.

 

[tlc53]

It doesn't appear to even like the code being in there.
It returns;
Compile Error: Ambiguous name detected: Worksheet_Change

 

[Fluff]

That error shows that you have two (or more)

Private Sub Worksheet_Change(ByVal Target As Range)

events in the same module, which you cannot have.
However change events do not recognise changes that occur due to a Checkbox being clicked.
You will need to link the code to the check box.
Are your checkboxes form controls or ActiveX

 

[tlc53]

Thank you! Understood. They are currently form controls but I gather I'd need to use ActiveX.
I may re-think this all entirely though.

 

[Fluff]

ActiveX won't make any difference, they still wont trigger a change event.
Do you just have the one checkbox, or multiple checkboxes?

 

[tlc53]

I've decided to try and work something with Data Validation to highlight when they haven't selected the check box/period ending date if they're entering data in that row.

However, if you don't mind, is there any chance you could help me another query I have. I've titled it "Combine VBA Codes together"