Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation problem
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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Data Validation | Excel Worksheet Functions | |||
Data Validation Problem | Excel Worksheet Functions | |||
Data Validation problem | Excel Discussion (Misc queries) | |||
Data validation problem | Excel Discussion (Misc queries) | |||
Data Validation problem. | Excel Discussion (Misc queries) |