Dynamically Building List

[Derek Hart]

I have seen how you can build a combo box in a cell by going to
Data...Validation...Allow:List and using a named range to define a list.
But this list cannot change with some management by removing the named range
and inserting or removing data from the range, and then naming the range
again. I wish to make it easy for a user to paste a column of data into the
same range, and then have that take automatically in the combo box list. Any
ideas on clever ways to do this? Can I build the list dynamically using vba
..AddItem, or some other way?

 

[carlo]

You can build the range dinamically.

look at this thread, thereis a similar problem:
http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/0aa39ab413df2e6d#

hth

Carlo

 

[excelent]

Put this in ur sheet-module where u have the list
Dropdown is inserted on Sheet1 C1 - change to fit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A9000")) Is Nothing Then Exit Sub
Dim t, List
For t = 1 To Cells(9000, 1).End(xlUp).Row
If Cells(t, 1) <> "" Then List = List & "," & Cells(t, 1)
Next
With Sheets("Sheet1").Range("C2").Validation ' where the dropdown will bee
present
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
End Sub



"Derek Hart" skrev:

 

[Derek Hart]

OK, I have two lists in a worksheet. After one item is chosen from the combo
list, I wish to set new data into the next list in the next column. Do these
combo lists have an after update event, or is there some way to do this?

Derek

 

[carlo]

Hey Derek

could you give us a small example how your list looks like?
It would improve the quality of the answers a lot.

cheers

Carlo