unlock cell based on formula

[Guest]

I have a drop down menu titled "Reason" in Column A. Column B is protected
(locked). When the "other" seletion is made, I want to be able to unlock the
cell to the right so that someone can enter a reason. Can this be done with
either a formula or VBA?

Thanks!
huber57

 

[Dave Peterson]

Depends on how you made the dropdown and what version of excel you're using.

I'm gonna guess that you used Data|validation for the dropdown and you're not
using xl97.

If these are true, then right click on the worksheet tab that should have this
behavior and select view code. Then paste this code into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPWD As String

myPWD = "hi"

With Target
'one cell at a time
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("a:a")) Is Nothing Then
'do nothing
Else
If .Value = "" Then
'do nothing
Else
Me.Unprotect Password:=myPWD
.Offset(0, 1).Locked = False
Me.Protect Password:=myPWD
End If
End If
End With

End Sub