Unlock a cell from answer to question

[lesley burlingham]

I have a cell called Quotation. I only want to be able to input data into this cell when the answer to a question is 3.
The answer is on sheet 2 and the quotation cell is on sheet 1. Is this possible.

 

[Somendra Misra]

Hi,

Try this:

Suppose your answer 3 is going to be on Sheet2 A1 cell, and your Quotation cell is on Sheet1, than put below function in Custom Data Validation formula:

=Sheet2!$A$1=3

Regards,

 

[lesley burlingham]

Hi

Thanks for this. Is there any way of getting the cell to be locked instead. I have the following code:


Script below:

ActiveSheet.Unprotect Password:="test"
If Sheet2.Range("C98").Value =3 Then
  Me.Range("L47").Locked =False
Else
  Me.Range("L47").Locked =True
EndIf
Me.Protect ("test")

I am getting the error below.

Runtime error 1004 - Unable to set the Locked property of the Range class

 

[NARAYANK991]

Hi Lesley ,

Try the code after it has been formatted in your last post.

I assume you have entered the code in a worksheet section , not in a module.

Narayan

 

[lesley burlingham]

I have the code in a worksheet section. I am still getting the following error:
Runtime error 1004 - Unable to set the Locked property of the Range class

 

[NARAYANK991]

Hi Lesley ,

Sorry , try this :

Public Sub Unlock_Cell()
          With Me
                .Unprotect Password:="test"
                If Sheet2.Range("C98").Value = 3 Then
                  .Range("L47").Locked = False
                Else
                  .Range("L47").Locked = True
                End If
                .Protect ("test")
          End With
End Sub

Narayan

 

[lesley burlingham]

Absolutely brilliant. You cracked it.