Need help removing items from combobox

[kylekelsch]

I'm not sure if this can be done and if it can I may be going at it
all wrong, but I have a combobox that adds items as I type, say if I
type "B" it adds all items from my list that begin with "B", Now what
I'm trying to do is make it so when I type the next letter it deletes
all the items from my combobox that do not contain those first 2
leters say if I type "Ba" anything that does not start with these 2
letters are removed and so on and so forth until when I finish typing
the word all I have in the list is that word.

Heres what I have so far. I'm new to VBA and know that I'm doing it
wrong but this is kind of how I figured it sould be, accept I don't
really know how to go about the removeitem part. Any input would be
greatly appreciated.

Private Sub ComboBox1_Change()

Dim ntotal As String

If Len(ComboBox1) > 0 Then
With Worksheets("Sheet1").Columns(1)
ntotal = 0
Do
If Left(Worksheets("Sheet1").Range("A1").Offset(ntotal,
0), Len(ComboBox1)) = _
Left(ComboBox1, Len(ComboBox1)) Then
ComboBox1.AddItem
(Worksheets("Sheet1").Range("A1").Offset(ntotal, 0).Value)
End If
With ComboBox1
If Left(Worksheets("Sheet1").Range("A1").Offset(ntotal,
0), Len(ComboBox1)) <> _
Left(ComboBox1, Len(ComboBox1)) Then
ComboBox1.RemoveItem
(Worksheets("Sheet1").Range("A1").Offset(ntotal, 0).Value)
End If
End With
ntotal = ntotal + 1
Loop Until Worksheets("Sheet1").Range("A1").Offset(ntotal,
0).Value = ""
End With

End If

End Sub

 

[Guest]

Private Sub ComboBox1_Change()

Dim ntotal As String

If Len(combobox1) > 0 Then
combobox1.Clear
With Worksheets("Sheet1").Columns(1)
ntotal = 0
Do
If Left(Worksheets("Sheet1").Range("A1").Offset(ntotal, 0),
Len(combobox1)) = _
Left(combobox1, Len(combobox1)) Then
combobox1.AddItem
(Worksheets("Sheet1").Range("A1").Offset(ntotal, 0).Value)
End If
ntotal = ntotal + 1
Loop Until Worksheets("Sheet1").Range("A1").Offset(ntotal,
0).Value = ""
End With

End If

End Sub

try that, clear the combo box first adn don't worry about removing
individual items

 

[kylekelsch]

I've already tryed that. It Clears the list including what I have
already typed and adds the entire list to the combobox.

 

[Guest]

Dim CTrigger as integer
Dim CurV as string
Private Sub ComboBox1_Change()
if ctrigger = 1 then exit sub
Dim ntotal As String

If Len(combobox1) > 0 Then
curv = combobox1
ctrigger = 1
combobox1.Clear
combobox1 = CurV
ctrigger = 0
With Worksheets("Sheet1").Columns(1)
ntotal = 0
Do
If Left(Worksheets("Sheet1").Range("A1").Offset(ntotal, 0),
Len(combobox1)) = _
Left(combobox1, Len(combobox1)) Then
combobox1.AddItem
(Worksheets("Sheet1").Range("A1").Offset(ntotal, 0).Value)
End If
ntotal = ntotal + 1
Loop Until Worksheets("Sheet1").Range("A1").Offset(ntotal,
0).Value = ""
End With

End If

End Sub

Dim CTrigger as integer
Dim CurV as string
put those at the top of the module, that should store the value of combbox
text field, clear the entries then restore your typing w/o retriggering the
event.

 

[kylekelsch]

Ben
That did as you said it would but I'm not quite getting the effect I'm
after.
The autocomplete is causing it to have problems. When I type "B" it
autocompletes "Base" there for the result I get are only items that
begin with "Base" furthermore when I press the down arrow to get to
the item I want It makes it so the list only contains items that begin
with the first item of the list. Example

preschool
prealgebra
prepare
president

If I type in "pre" it might show the above results but when I press
the down arrow to try to get to "prepare" as soon as it sees
"preschool" it narrows the list down to things that only begin with
"preschool".

I'm stumped on this and I don't really know if I'm trying for an
unreachable goal. but if you have any other ideas I would appreciate
them.