Toggle cell values

Posted by Peter Bellinger on February 15, 2002 2:36 PM

How can you toggle the values of other cells.

The following example may help:-

Cells A1 to E1 must contain one 'Y' and the rest 'N's i.e. as a user types 'Y' into any of one cells the rest turn to 'N'

PS. Sorry if this has been asked many times before but I could not find the answer.

Thanks

Pete

Posted by Osric on February 15, 2002 3:37 PM

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = [A1:E1]
If Not Intersect(Target, rng) Is Nothing Then
If Target.Value &LT;&GT; "Y" Then
MsgBox "Only Y can be entered in cells A1:E1."
Exit Sub
End If
Application.EnableEvents = False
rng.Value = "N"
Target.Value = "Y"
End If
Application.EnableEvents = True
End Sub

Posted by Peter Bellinger on February 16, 2002 12:33 AM

Thanks for time and support - Works great !!

Cheers

Pete