MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Data Validation - URGENT PLEASE HELP

Posted by Elodie on July 31, 2001 3:45 AM

Could anyone please let me know urgently how you can set data validation for a combo box please. I want to stop the user entering duplicate enteries from the combo box. Or if there is any VBA code that anyone knows to do this that would be great.

Cheers for you help.


The VBA code used to set up my combo box is:
Sub AddDropDown(Target As Range)

Dim ddBox As DropDown
Dim vaProducts As Variant
Dim i As Integer

vaProducts = Array(Sheet2.Cells.Range("A1"), Sheet2.Cells.Range("B1"), Sheet2.Cells.Range("C1"), Sheet2.Cells.Range("D1"), Sheet2.Cells.Range("E1"), Sheet2.Cells.Range("F1"), Sheet2.Cells.Range("G1"), Sheet2.Cells.Range("H1"))
With Target
Set ddBox = Sheet3.DropDowns.Add(.Left, .Top, .Width, .Height)
End With
With ddBox
.OnAction = "EnterProdInfo"
For i = LBound(vaProducts) To UBound(vaProducts)
.AddItem vaProducts(i)
Next i
End With
End Sub

Private Sub EnterProdInfo()
Dim vaPrices As Variant

vaPrices = Array("A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1")
With Sheet3.DropDowns(Application.Caller)
.TopLeftCell.Value = .List(.ListIndex)
.TopLeftCell.Offset(0, 2).Value = vaPrices(.ListIndex - Array(0, 1)(1))
End With

Posted by Rob Jackson on July 31, 2001 4:18 AM

There are a few options, this is probably the easiest: set an boolean array to match your selections and toggle if used. Can easily be driven off the ListIndex value. So if List index 5 is chosen it flips the Array(5)=True. This can then be used to check if previously used. A similar method could be used implement bound columns and writing to a sheet. The final option is to run a scan on your records to see if it has been used. This would be slower but suitable if poeple are going to be in and out of the worksheet updating at different times.

Hope this helps..


Posted by Becky on July 31, 2001 8:28 AM

Rob How do I do this please


How do I go about doing this please.