Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
building list | Excel Worksheet Functions | |||
dynamically building references to named ranges | Excel Discussion (Misc queries) | |||
building a list in a col...based on another col | Excel Programming | |||
self-building dropdown list | Excel Worksheet Functions |