Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dymanically add to validation list
I am using XL 2002
I have a cell in which I have set it's values to a validation list of sales people. Is is possible to update the list by having the user type in a new sales person's name and then have code add the name to the validation list? I don't think so, but I thought that I would ask the experts Thanks Joe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dymanically add to validation list
Hi
see http://www.contextures.com/excelfiles.html and look for the example file: Update Validation List -- Regards Frank Kabel Frankfurt, Germany Joe wrote: I am using XL 2002. I have a cell in which I have set it's values to a validation list of sales people. Is is possible to update the list by having the user type in a new sales person's name and then have code add the name to the validation list? I don't think so, but I thought that I would ask the experts. Thanks, Joe |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dymanically add to validation list
Thanks you
----- VBAexpert wrote: ---- Hi Mike Yes it is possible to do this. As an example, the following code will add anything entered in column D to the validation list in cell B4 Private Sub Worksheet_Change(ByVal Target As Range If Target.Column = 4 The Dim OldList As Strin With [b4].Validatio OldList = .Formula .Delet .Add Type:=xlValidateList, Formula1:=OldList & "," & Target.Tex End Wit End I End Su This code must be placed in the worksheet's event code module. To do this, right-click on the worksheet tab, select View Code, and paste the code into the Code Pane This code assumes that the validation list is a literal list, NOT a range containing a list. For example, the list could be "joe, fred, george, susan, mike". The limitation of this is that I believe the length of the string cannot exceed 255 characters. If you want to enable the list to have arbitrary (i.e., very large) length you would have to use the range reference method, which would mean that the code would have to add the name to the range list. You could then keep from having to update the cell's validation list by using a Dynamic Range reference as the validation list. If you want the code for this, send me an email Keep Excelling Damon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dymanically generate chart, based on multiple values | Charts and Charting in Excel | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming |