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

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

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