Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox list
Hi,
Is it possible to have the contents of a combobox list filterd depending on the contents of a partcular cell. e.g. Lets say I have a data range on sheet1 A1:G1000 combobox1 uses range sheet1 A1:1000 for it's list, this does not have does not have unique records in the range and returns the selected value to cell a1 on sheet2. Q1. Can this be set to return unique values in the list? I then want the contents of the list for combobox2 to use the range Sheet1 B1:1000 (which has unique record) but be filterd by the selection made in combobox1. This will save users scroling through 1000 records to find the item in the list in combobox2 Thanks Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox list
Pete, you can use the advanced filter to filter the first list to a hidden
sheet, and then use the entries on that sheet to populate the combobox: (untested) 'clear destination range thisworkbook.sheets(1).columns(1).clear 'filter data range sheets("sheetwithdata").Range("A1:A1000").Advanced Filter Action:=xlFilterCopy, _ CopyToRange:=thisworkbook.sheets(1).Range("a1"), Unique:=True dim lastcell as range 'get last cell with entry set lastcell = thisworkbook.sheets(1).cells(65536,1).end(xlup) 'get first cell set startcell = thisworkbook.sheets(1).range("a1") 'set row source userform1.combobox1.rowsource = range(startcell,lastcell).address(exterenal:=true) sheets("sheet2).range("a1").value = userform1.combobox1.value You could use a similar approach for filtering the list for the second combo box. Bob Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Pete" wrote in message ... Hi, Is it possible to have the contents of a combobox list filterd depending on the contents of a partcular cell. e.g. Lets say I have a data range on sheet1 A1:G1000 combobox1 uses range sheet1 A1:1000 for it's list, this does not have does not have unique records in the range and returns the selected value to cell a1 on sheet2. Q1. Can this be set to return unique values in the list? I then want the contents of the list for combobox2 to use the range Sheet1 B1:1000 (which has unique record) but be filterd by the selection made in combobox1. This will save users scroling through 1000 records to find the item in the list in combobox2 Thanks Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ComboBox dropdown list? | Excel Discussion (Misc queries) | |||
.AddItem list and populating combobox with created list | Excel Discussion (Misc queries) | |||
dependant combobox list | New Users to Excel | |||
ComboBox List | Excel Programming | |||
ComboBox List | Excel Programming |