Conditional Cell Locking

[davidrusbridge]

Can someone suggest the VBA code that will allow the following scenario:

I am using Excel 2003 with a single sheet. The majority of the cells are locked apart from 8 cells which can be selected

Cell R:20 is normally locked

Cell C:12 has a drop down box with 5 text data type choices available. If choice 5 is selected I want cell R:20 to unlock for number entry. If any other choice is made I want cell R:20 to remain locked and preferably display a zero

The sheet will be password protected so I assume the code will have to declare a variable containing the password so it can unprotect the sheet, unlock the cell then protect the sheet again?

Any help will be gratefully received

 

[lenze]

Hi and welcome to the board!!
In the sheet module, use something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$12" Then Exit Sub
If Target = "YourText" Then
   ActiveSheet.Unprotect "password"
   Range("$R$20").Locked = False
   ActiveSheet.Protect "password"
End If
End Sub

lenze

 

[davidrusbridge]

Hi lenze,

Thank you for the welcome and the quick response!

I have tried the code and it will in unprotect the sheet but is throwing up a run time error with the following statement:

"Run-time error '1004'
Unable to set the Locked property of the Range class"

Any ideas?

 

[lenze]

The code runs for me!!
Is R20 a merged cell???? If so, you will have problems.!!
lenze

 

[davidrusbridge]

lenze,

Great spot! I did have the cell merged which I have now unmerged and it works - sortof

When I select item 5 in C12 it does what I need but if I then select item 1,2,3 or 4 it is leaving R:20 unlocked?

Is it also possible to set the cell value to zero if items 1,2,3 or 4 are selected in C12?

 

[lenze]

See if this is better!!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$12" Then Exit Sub
ActiveSheet.Unprotect "password"
If Target = "YourText" Then
   Range("$R$20").Locked = False
   Range("$R$20") = ""
Else
   Range("$R$20").Locked = False
   Range("$R$20") = 0
   Range("$R$20").Locked = True
End If
ActiveSheet.Protect "password"
End If
End Sub

lenze

 

[davidrusbridge]

lenze,

Thank you for your help. I have managed to get it working with a few tweaks to your suggested code