ExcelBanter - 4 cells but only one entry

Mike,

You could use the worksheet change event.

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

Change

myList = "1,2,3,4"

to your list of allowed values for the dropdown, and change

Set myER = Range("B1:E20")

To the four-column wide range where you want this to occur.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myR As Range
Dim myER As Range
Dim myNum As Integer
Dim i As Integer
Dim myList As String

myList = "1,2,3,4"
Set myER = Range("B1:E20")

If Intersect(Target, myER) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Set myR = Intersect(myER, Target.EntireRow)

With myR.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=COUNTA(" & myR.Address & ")=1"
.IgnoreBlank = False
.InCellDropdown = False
.InputTitle = ""
.ErrorMessage = "You can only enter one value into the four cells."
.ShowError = True
End With

myNum = 0
For i = 1 To 4
If myR(1, i).Value = "" Then
myNum = myNum + 1
End If
Next i

If myNum < 4 Then Exit Sub

With myR.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=myList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorMessage = "You can only enter values from the dropdown list."
.ShowError = True
End With

End Sub

"mike" wrote in message
...
i wonder if someone can help.
i am producing a database in excel and i have 4 cells in a row next to each
other each with a drop down list(done through datavalidations)(i still need
the drop down list), but i cant seem to figure out, if one of the boxes has
an entry how do i stop the other 3 from recieving entries??

thanks
mike