Locking Cells on Validation

[tahir]

Hi Folks,

I have a worksheet that has got 2 Columns, A and B. Both Columns have validation list with values "YES" and "NO", My query is that for example if on cell A10 if i choose Value "YES" from Validation drop down list then the cell B10 should automatically Show "NO" and should be locked for protection and no changes be made to cell B10. But if i select "NO" in A10 then B10 should show "YES" and now A10 should be locked.

I have been trying trying and trying but its not happening, Please help me on this.

Regards,


Tahir

[Jim Rech]

That cannot be done through Data, Validation alone since it only affects the
current cell. A better design is to use a worksheet change event macro to
make the cells in A and B the opposite whenever one changes. Forget about
the locking bit; totally unnecessary.

--
Jim
"tahir" <[email protected]> wrote in
message news:[email protected]...
|
| Hi Folks,
|
| I have a worksheet that has got 2 Columns, A and B. Both Columns have
| validation list with values YES and No, My query is that for example if
| on cell A10 if i choose Value YES from Validation drop down list then
| the cell B10 should automatically Show No and should be locked for
| protection and no changes be made to cell B10. But if i select No in
| A10 then B10 should show YEs and now A10 should be locked.
|
| I have been trying trying and trying but its not happening, Please help
| me on this.
|
| Regards,
|
|
| Tahir
|
|
| --
| tahir
|
| Student, working on a project
| ------------------------------------------------------------------------
| tahir's Profile:
http://www.excelforum.com/member.php...fo&userid=6053
| View this thread: http://www.excelforum.com/showthread...hreadid=523016
|

[tahir]

That cannot be done through Data, Validation alone since it only affects the
current cell. A better design is to use a worksheet change event macro to
make the cells in A and B the opposite whenever one changes. Forget about
the locking bit; totally unnecessary.

--
Jim
"tahir" <[email protected]> wrote in
message news:[email protected]...
|
| Hi Folks,
|
| I have a worksheet that has got 2 Columns, A and B. Both Columns have
| validation list with values YES and No, My query is that for example if
| on cell A10 if i choose Value YES from Validation drop down list then
| the cell B10 should automatically Show No and should be locked for
| protection and no changes be made to cell B10. But if i select No in
| A10 then B10 should show YEs and now A10 should be locked.
|
| I have been trying trying and trying but its not happening, Please help
| me on this.
|
| Regards,
|
|
| Tahir
|
|
| --
| tahir
|
| Student, working on a project
| ------------------------------------------------------------------------
| tahir's Profile:
http://www.excelforum.com/member.php...fo&userid=6053
| View this thread: http://www.excelforum.com/showthread...hreadid=523016
|

This is the code of Worksheet Change, This code works great, but it has limitations. The code only triggers on Cell B3 for change. Is it possible to expand it to a range of A1 to L60

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Application.EnableEvents = False
If Target.Value = "Yes" Then
With Range("C3")
.Value = "No"
With .Validation
.Delete
.Add Type:=xlValidateTextLength, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Leave cell blank"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("C3")
.Value = "Yes"
With .Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="=Eligible"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Select from the list"
.ShowInput = False
.ShowError = True
End With
End With
End If
End If
Application.EnableEvents = True
End Sub