Restricting Cell Input (2000/2003)

Topic

New Reply

I have a worksheet where I want the user to input an x in a cell, but only one cell on a row.
If the cells are b4,c4, and d4, -and the user places an x in any one of these cells. can the other two cells be made unavailable for entry.
At the beginning all 3 cells are available for entry. If an “x ” is originally placed in b4-then c4 and d4 wouldn’t allow an entry. If an x is originally placed in c4-then b4 and d4 wouldn’t allow an entry
the same for an entry in d4 —b4 and c4 wouldn’t allow an entry.
Any ideas or help would be appreciated.

Reply | Quote

Replies

Select B4:D4
Select Data | Validation.
Select Custom from the Allow dropdown list.
Enter the following formula in the box:

=($B$4&$C$4&$D$4=”x”)

Clear the check box “Ignore blank”.
Activate the ‘Error message’ tab.
Make sure that Stop is selected as style, and enter a suitable error message.
You can also enter an instructive text in the ‘Input message’ tab if you wish.
Click OK.

Reply | Quote

That did it. I was thinking data validation but I would have never got that figured out thanks so much.

Reply | Quote

If you would like the user to be able to change his mind after typing an X in one of cells by typing an X in one of the other cells and having the previous X cleared, the code below entered into the event module behind the worksheet will do this.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4:D4")) Is Nothing Then
Application.EnableEvents = False
If Not Intersect(Target, Range("B4")) Is Nothing Then
If (UCase(Range("B4").Value) = "X") Or (Range("B4").Value = "") Then
Range("B4").Value = UCase(Range("B4").Value)
Range("C4").Value = ""
Range("D4").Value = ""
Else
Range("B4").Value = ""
Range("B4").Select
MsgBox "Cell B4 can only contain an X"
End If
End If
If Not Intersect(Target, Range("C4")) Is Nothing Then
If (UCase(Range("C4").Value) = "X") Or (Range("C4").Value = "") Then
Range("B4").Value = ""
Range("C4").Value = UCase(Range("C4").Value)
Range("D4").Value = ""
Else
Range("C4").Value = ""
Range("C4").Select
MsgBox "Cell C4 can only contain an X"
End If
End If
If Not Intersect(Target, Range("D4")) Is Nothing Then
If (UCase(Range("D4").Value) = "X") Or (Range("D4").Value = "") Then
Range("B4").Value = ""
Range("C4").Value = ""
Range("D4").Value = UCase(Range("D4").Value)
Else
Range("D4").Value = ""
Range("D4").Select
MsgBox "Cell D4 can only contain an X"
End If
End If
Application.EnableEvents = True
End If
End Sub


The attached workbook demonstrates the code.

Reply | Quote

Thanks this works good also.

Reply | Quote