Unprotect a cell if IF Statement is true

Posted by Bill.M on March 05, 2001 2:42 PM

Hi All,

Is it possible to unprotect a cell by testing the value of another cell. For exmample =IF($B4="phone rental",&LT;unlock C4>,"") I have used &LT;unlock C4> for the part that I don't know. I imagine that VBA will be involved somewhere but I am a bit of a novice whne it comes to that.

Appreciate your help.

Bill M

Posted by David Hawley on March 05, 2001 2:55 PM

Hi Bill

You will need VBA for this.

Right click on the sheet name tab and paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("B4") = "PhoneRental" Then
Me.Unprotect Password:="secret"
Me.Range("A1").Locked = False
Me.Protect Password:="secret"
End If
End Sub

Changes ranges to suit. Push Alt+Q and save.

Dave

OzGrid Business Applications

Posted by Bill M on March 06, 2001 11:53 AM

Hi Dave,

Thanks for your quick response and of course for the answer. I don't need to tell you that your solution works just fine. Even with my limited knowledge I can just about follow what's going on. However could you explain what is the significance of Me at the beginning of each line?

Thanks again.

Bill

Posted by David Hawley on March 06, 2001 2:25 PM

Sure Bill, The "Me" is a generic keyword that can be used in VBA and will always refer to the Object that houses the Module it is used in. So, as this code resides in the Worksheet module it will refer to the Worksheet itself. If I used "Me" in the Workbook module it would refer to the Workbook itself. You could also use it in a UserForms Module to refer to the UserForm.

The big advantage of using "Me" is that if the Worksheet, Workbook or UserForm name changes it doesn't matter.

Dave
OzGrid Business Applications

Posted by Bill M on March 07, 2001 12:40 AM

Thanks for taking the time Dave you've been a real help.

Bill