If Function Question

[Damil4real]

I have a listbox in cell a15 with a Yes & No. If user picks "yes",
lock cell a16 because the question will not be applicable, and I don't
want the user to choose any option.

How do I do that in excel?

Thanks!

 

[Bernie Deitrick]

Damil,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

Your worksheet should be protected, with a password - the password assumed below can be changed.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Const PW As String = "Password"
If Target.Address <> "$A$15" Then Exit Sub
With Target.Offset(1, 0)
Target.Parent.Unprotect PW
If Target.Value = "No" Then
.Locked = True
.Interior.ColorIndex = 3
Else
.Locked = False
.Interior.ColorIndex = xlNone
End If
End With
Target.Parent.Protect PW
End Sub

 

[Pete_UK]

You can't lock a cell with a formula (and the sheet would need to be
protected anyway, for it to have any effect), but what you might want
to do is apply conditional formatting to cell A16 so that the
foreground colour is changed to white if A15 ="Yes", so it appears as
if no question is being asked.

Hope this helps.

Pete