Locking Cells on Validation

[tahir]

Hi Folks,

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

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

Regards,


Tahi

 

[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
message |
| 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?action=getinfo&userid=6053
| View this thread: http://www.excelforum.com/showthread.php?threadid=523016
|

 

[tahir]

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

--
Jim
message |
| Hi Folks,
|
| I have a worksheet that has got 2 Columns, A and B. Both Column
have
| validation list with values YES and No, My query is that for exampl
if
| on cell A10 if i choose Value YES from Validation drop down lis
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, Pleas
help
| me on this.
|
| Regards,
|
|
| Tahir
|
|
| --
| tahir
|
| Student, working on a project

------------------------------------------------------------------------
| tahir's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=6053
| View this thread
http://www.excelforum.com/showthread.php?threadid=523016
|

This is the code of Worksheet Change, This code works great, but it ha
limitations. The code only triggers on Cell B3 for change. Is i
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 Su