Data Validation problem

waltersanglea · 04-21-2005, 02:06 AM

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!

Patrick Molloy · 04-21-2005, 03:06 AM

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!
>
>

**MartinShort** · 04-21-2005, 03:18 AM

If I was doing it, I would set up a combo box on your sheet using the control toolbox toolbar. When you've set it up, with the design button pressed, double click the combo. It should take you to the VBA (Macro Editor).

1. I would always suggest modifying the name of your combo to follow Reddick Naming Conventions so in this case I modifed the name property to read "cboAnimals" (ie Combo Animals)
2. You will need to modify the code to be cboAnimals rather than Microsoft's choice of ComboBox1 - your call!
3. Now modify the ListFill property to be A1:A3 {ie your entries cat, dog, fish will be in these cells)
4. Now modify the Linked Cell property to read B1 {ie where your selection is output)

You can stop now and check that it works ok and allows you to select an animal from your list A1:A3. (Don't forget to click on the Design Button on the Control Box Toolbar to take it out of design mode)

Now reclick design button, double click your combo and put the following code into your Visual Basic Editor.

Please Login or Register  to view this content.

All this does is each time the combo is changed, it sets up a counter called i. Then each time through the loop it looks at A1 then A2 then A3 and so on to see if the cell is blank. If it is blank the loop ends (ie Do Until!) Lastly, it altered the ListFillRange Property you manually set earlier to be A1:A3 to be the A1:A?. Of course "?" is one less than our counter i so it will encompass everything up to but not including the first blank cell. Now every time you add an entry to your range A1:A?, it will automatically recompile the dropdown list for you.

I hope this helps and isn't too confusing. You'd probably find someone with years of experience would do this a different way, but I find this works for me.

ps Pat Molloys solution is a lot swisher and more sophisticated than mine with bells and whistles. If you can do and understand both methods it's well worth it.

pps I started out on VBA around Christmas. The best book I've found is Excel 2002 Visual Basic For Applications Step by Step
Excel 2002 Visual Basic For Applications Step by Step

Bob Phillips · 04-21-2005, 05:06 AM

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)

"waltersanglea" <[email protected]> wrote in
message news:[email protected]...
> 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!
>

Debra Dalgleish · 04-21-2005, 05:06 AM

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...m#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

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!
>

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html