Data Validation problem

[waltersanglea]

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 t
control this list. I need the user to be able to add to this list i
he/she does not find the item that they need. For example, the lis
contains cat, dog, and fish but the user needs to use bird. Sinc
bird is not in the list I want the user to be able to add bird. S
now the list is cat, dog, fish, and bird

Can this be done? If so, please give me some pointers or other place
to research.

Also, I would like to learn more about programming in excel and t
learn more about what the functions can do for me. Does anyone kno
where I should start looking

Thanks for your help

 

[Patrick Molloy]

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

 

[Bob Phillips]

Doing it a bit manually, you could have the DV list in a worksheet range,
say A1:A10, and refer to this in the DV definition dynamically, that is
using a formula of

=OFFSET(A1,,,COUNTA(A:A))

If the user finds a value not in the list, the just append to A1:A10, or
whatever it has grown to.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Debra Dalgleish]

There's a sample workbook here that updates a validation list:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'Update Validation List' or 'Update
Multiple Validation Lists'

For information on programming, see David McRitchie's list of VBA tutorials:

http://www.mvps.org/dmcritchie/excel/excel.htm#vbatutorials

And for information on Functions, you can download Peter Noneley's
Function dictionary at Ron de Bruin's site:

http://www.rondebruin.nl/id.htm