Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help removing items from combobox
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(ntota l, 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(ntota l, 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help removing items from combobox
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(ntota l, 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 -- When you lose your mind, you free your life. Ever Notice how we use '' for comments in our posts even if they aren''t expected to go into the code? " wrote: 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(ntota l, 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(ntota l, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help removing items from combobox
I've already tryed that. It Clears the list including what I have
already typed and adds the entire list to the combobox. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help removing items from combobox
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(ntota l, 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. -- When you lose your mind, you free your life. Ever Notice how we use '' for comments in our posts even if they aren''t expected to go into the code? " wrote: I've already tryed that. It Clears the list including what I have already typed and adds the entire list to the combobox. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help removing items from combobox
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combobox items determined by the selection in another combobox | Excel Programming | |||
Removing items from listbox | Excel Programming | |||
Removing item from combobox with vba | Excel Programming | |||
Removing item from combobox with vba | Excel Programming | |||
Removing item from combobox with vba | Excel Programming |