Visual Basic: Combobox Complications
 

Alrighty, so I have this code to filter out a combobox and
it works! However there are some complications:

1) Some combo boxes require data from two worksheets from
the current workbook. What my does is filter the data
from the first worksheet, then process the filtering of
hte second worksheet. Then it adds this filtrated value
in the combo box. Can someone correct my code so that it
will obtain teh data from both worksheets, then filte? I
mean it's kind of annoying when it says something like

0
100
300
800
0
20
etc. ya know?

2) Despite the unfiltrated filtration =) my code makes my
combobox do something odd. If i were to play my code, the
firt time I click the combobox, the values appear fine.
However, when I click on the same combobox a second time,
it shows twice the data. And if I click it a third time,
it shows thrice the original data. Can someone help me
fix this problem so that no matter how many times i click
the combo box, it'll only show my data one?

Here is my filtration code:
For i = 1 To NoDupes1.Count - 1
For j = i + 1 To NoDupes1.Count
If NoDupes1(i) NoDupes1(j) Then
Swap1 = NoDupes1(i)
Swap2 = NoDupes1(j)
NoDupes1.Add Swap1, befo=j
NoDupes1.Add Swap2, befo=i
NoDupes1.Remove i + 1
NoDupes1.Remove j + 1
End If
Next j
Next i

Thanks a whole bunch anyone =)

Visual Basic: Combobox Complications
 

you could try this:

By using a collection and ignoring the errors it generates when you try
to insert an item with the same key it's easy to filter out the doubles.

dim col as collection
dim var as variant
dim rng as range
dim cell as range

'important to ignore the errors
on error resume next

'initialize the collection
set col = new collection
'set the 1st range

set rng=worksheets(1).range("a1:a100")

for each cell in rng.cells
col.add cell.value , cstr(cell.value)
next

'add the 2nd range cells to the same collection..

set rng=worksheets(2).range("b10:b150")
for each cell in rng.cells
col.add cell.value , cstr(cell.value)
next

'now you have 1 clean (unsorted) collection
'all you need to do is make it into a 0based variant

redim var (0 to col.count -1)
for i = 1 to col.count
var(i-1)=col(i)
next

'dump it in the combo
combobox.list=var
'done!


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Charlie" wrote:

Alrighty, so I have this code to filter out a combobox and
it works! However there are some complications:

1) Some combo boxes require data from two worksheets from
the current workbook. What my does is filter the data
from the first worksheet, then process the filtering of
hte second worksheet. Then it adds this filtrated value
in the combo box. Can someone correct my code so that it
will obtain teh data from both worksheets, then filte? I
mean it's kind of annoying when it says something like

0
100
300
800
0
20
etc. ya know?

2) Despite the unfiltrated filtration =) my code makes my
combobox do something odd. If i were to play my code, the
firt time I click the combobox, the values appear fine.
However, when I click on the same combobox a second time,
it shows twice the data. And if I click it a third time,
it shows thrice the original data. Can someone help me
fix this problem so that no matter how many times i click
the combo box, it'll only show my data one?

Here is my filtration code:
For i = 1 To NoDupes1.Count - 1
For j = i + 1 To NoDupes1.Count
If NoDupes1(i) NoDupes1(j) Then
Swap1 = NoDupes1(i)
Swap2 = NoDupes1(j)
NoDupes1.Add Swap1, befo=j
NoDupes1.Add Swap2, befo=i
NoDupes1.Remove i + 1
NoDupes1.Remove j + 1
End If
Next j
Next i

Thanks a whole bunch anyone =)

Visual Basic: Combobox Complications
 

the code you show looks like the bubble sort portion of the code at John
Walkenbach's site for filling a listbox with a unique list. While
Keepitcool has suggested using a collection, that code has already performed
that step. You would just need to adjust the code to fill the existing
collection with data from both sources.

Regards,
Tom Ogilvy

"Charlie" wrote in message
...
Alrighty, so I have this code to filter out a combobox and
it works! However there are some complications:

1) Some combo boxes require data from two worksheets from
the current workbook. What my does is filter the data
from the first worksheet, then process the filtering of
hte second worksheet. Then it adds this filtrated value
in the combo box. Can someone correct my code so that it
will obtain teh data from both worksheets, then filte? I
mean it's kind of annoying when it says something like

0
100
300
800
0
20
etc. ya know?

2) Despite the unfiltrated filtration =) my code makes my
combobox do something odd. If i were to play my code, the
firt time I click the combobox, the values appear fine.
However, when I click on the same combobox a second time,
it shows twice the data. And if I click it a third time,
it shows thrice the original data. Can someone help me
fix this problem so that no matter how many times i click
the combo box, it'll only show my data one?

Here is my filtration code:
For i = 1 To NoDupes1.Count - 1
For j = i + 1 To NoDupes1.Count
If NoDupes1(i) NoDupes1(j) Then
Swap1 = NoDupes1(i)
Swap2 = NoDupes1(j)
NoDupes1.Add Swap1, befo=j
NoDupes1.Add Swap2, befo=i
NoDupes1.Remove i + 1
NoDupes1.Remove j + 1
End If
Next j
Next i

Thanks a whole bunch anyone =)