Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
4 cells but only one entry
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove last <li entry from cells | Excel Discussion (Misc queries) | |||
Best way to format cells for entry... | New Users to Excel | |||
Cell Entry That Locks Selected Cells From Any Data Entry. | Excel Worksheet Functions | |||
Force entry to one of three cells | Excel Worksheet Functions | |||
How to you count cells that contain any entry? | Excel Discussion (Misc queries) |