Many Tried It Byt Can U...

[tahir]

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

[Trevor Shuttleworth]

Tahir

change:

If Target.Address = "$B$3" Then

to:

If Intersect(Target,Range("A1:L60")) is Nothing Then Exit Sub

and remove the last End If

Regards

Trevor


"tahir" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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=523248
>

[tahir]

I did as u recommended but now i have a scenario, I want to choose Yes from A1 and The result in B1 shows No thats fine but i want to do the same treatment to all the cells in A column and B column. is it possible, follwoing is the code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a1:b10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Range("a1")
If Target.Value = "Yes" Then
With Range("b1")
.Value = "No"
End With
Else
With Range("b1")
.Value = "Yes"
End With
End If
Application.EnableEvents = True
End With
End Sub

[Trevor Shuttleworth]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = "Yes" Then
Target.Offset(0,1).Value = "No"
Else
Target.Offset(0,1).Value = "Yes"
End If
Application.EnableEvents = True
End Sub

Regards

Trevor


"tahir" <[email protected]> wrote in
message news:[email protected]...
>
> I did as u recommended but now i have a scenario, I want to choose Yes
> from A1 and The result in B1 shows No thats fine but i want to do the
> same treatment to all the cells in A column and B column. is it
> possible, follwoing is the code
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Intersect(Target, Range("a1:b10")) Is Nothing Then Exit Sub
> Application.EnableEvents = False
> With Range("a1")
> If Target.Value = "Yes" Then
> With Range("b1")
> Value = "No"
> End With
> Else
> With Range("b1")
> Value = "Yes"
> End With
> End If
> Application.EnableEvents = True
> End With
> End Sub
>
>
> --
> 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=523248
>

[tahir]

Thanks Trevor and all who supported, One more question b4 i complete this problem, How is it possible to do the same treatment for Column C and D, also column E and F.

Thanks again

Regards,

Tahir