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
