Combobox list

[Pete]

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

 

[Bob Flanagan]

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").AdvancedFilter
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