Data Validation problem
I don't think that you can trap the dada validation error. That means the
value doesn't get entered, so a CHANGE event doesn't fire either.
One way would be to have another cell dedicated to adding values to a list.
Or you could use a userform.
In my example I have a cell range named 'InputCell' on Sheet1 --values
entered will beadded to my list. My list, on another sheet is range named
'DataList'
Elsewhere, I have a cell with datavalidation set to Kist and the source is
=DataList
This is the code on the Sheet1's code page
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rTable As Range
If Target.Value = "" Then Exit Sub
If Target.Address = _
ThisWorkbook.Names.Item("InputCell").RefersToRange .Address Then
Set rTable = ThisWorkbook.Names.Item("DataList").RefersToRange
With rTable
.Offset(.Rows.Count, 0).Resize(1, 1) = Target.Value
With .Resize(.Rows.Count + 1)
.Name = "DataList"
.Interior.ColorIndex = 34
End With
End With
Target.Value = ""
End If
End Sub
Its crude, but it checks if you entered a value in the InputCell. If you
did, that value
is added to the list and the input cell cleared.
Select the cell with datavalidation & you'll see the item in the list.
A userform is much easier, since you can use a combo with its MatchRequired
property set to False
Add a combobox (combobox1) to a userform. Add a command button (btnOK).
Set the RowSource of the combo to DataList ( from above)
add this code to the button's click event
Private Sub btnOK_Click()
If ComboBox1.ListIndex = -1 Then
If ComboBox1.Value = "" Then
MsgBox "Select a value!"
Exit Sub
End If
If MsgBox("AddItem to lIst?", vbYesNo) = vbYes Then
' call procedure to add item
End If
End If
End Sub
The code checks if the user entered something, and then if its on the list.
If th ecombo vaue is not "" then something was entered. If the ListIndex
property is -1 then nothing was selected.
HTH
"waltersanglea" wrote:
I am not sure if this can be done but here goes...
I have a cell with a drop down list and I am using Data Validation to
control this list. I need the user to be able to add to this list if
he/she does not find the item that they need. For example, the list
contains cat, dog, and fish but the user needs to use bird. Since
bird is not in the list I want the user to be able to add bird. So
now the list is cat, dog, fish, and bird.
Can this be done? If so, please give me some pointers or other places
to research.
Also, I would like to learn more about programming in excel and to
learn more about what the functions can do for me. Does anyone know
where I should start looking?
Thanks for your help!
|