Dynamically Building List
 

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?

Dynamically Building List
 

You can build the range dinamically.

look at this thread, thereis a similar problem:
http://groups.google.com/group/micro...39ab413df2e6d#

hth

Carlo

On Dec 27, 10:56*am, "Derek Hart" wrote:
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?

Dynamically Building List
 

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:

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?

Dynamically Building List
 

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


"excelent" wrote in message
...
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:

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?

Dynamically Building List
 

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

On Dec 28, 3:20*am, "Derek Hart" wrote:
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

"excelent" wrote in message

...



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:

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?- Hide quoted text -

- Show quoted text -