PDA

View Full Version : fill Combo box data acroding to text box data entry.



VISHAL120
09-20-2012, 10:08 PM
Hi All,

Can you please explain me how can i fill data in a combo box according to entry done in a text box.

example i have data with several entries like:

1201 RED 1201_RED
1201 RED 1201_RED
1201 BLUE 1201_BLUE
1201 BLUE 1201_BLUE
1202 VERT 1202_VERT
1202 VERT 1202_VERT
1203 yellow 1203_yellow
1203 yellow 1203_yellow

what i need is if i type 1201 then only :

1201_RED
1201_BLUE

is display not all the colors and number.

Am actually using this code which is adding all the colors:

Private Sub UserForm_Initialize()


optAddNew.Value = True
'Me.cboPOSEdit.Enabled = False

Me.cboPOSEdit.Enabled = True
Dim Nullstring



Dim sRange As Range


Set sRange = Range("C4", Range("C65536").End(xlUp))
FillCtrlUnique sRange, Me.cboPOSEdit
End Sub
Sub FillCtrlUnique(myRange As Range, myControl As Control)
Dim Coll As New Collection
Dim var As Variant
Dim cell As Range

On Error Resume Next
For Each cell In myRange
Coll.Add Item:=cell.Value, key:=CStr(cell.Value)
Next cell
On Error GoTo 0

With myControl
.Clear
For Each var In Coll
.AddItem var
Next var
End With
Set Coll = Nothing
End Sub
Please find also a sample of my data attached.

Any help will be very helpful to me.
thanking in advance.

snb
09-21-2012, 03:26 AM
Sub snb_002()
mycontrol.list = Filter([transpose(if(C1:C100="","~",if(countif(offset(C1,,,row(C1:C100)),C1:C100)=1,C1:C100,"~")))], "~", False)
end sub

VISHAL120
09-21-2012, 04:00 AM
Hi Snb,

many thanks for the help but i am not able to integrate the code. Can you please explain me where shall i integrate the code. is it on the text box change?

thanks again for your help.

snb
09-21-2012, 05:42 AM
Make it the userform_initialize event.

VISHAL120
09-21-2012, 05:50 AM
Hi Snb,

I have tried it but am having error like object required.

am attaching the file.

for your info.