Combo Box - auto complete and the bound column/text column propert

[jeffbert]

I am going to try to make this as clear as I can, as it is a little confusing
to me. Here goes:

I have an ActiveX combo box on my worksheet to select a Vendor. I have the
Column Count property set to 2, so that I can see the Vendor name (which is
in the first column of the fill range) and the vendor number (which is in the
second coulmn of the fill range).

I have the AutoWordSelect property set to True so that you can start typing
a Vendor name and the combo box jumps to the vendor starting with the letters
that you are typing. The MatchRequired property is set to True, ane the
MatchEntry property is set to 1-Match Entry Complete to ensure that the value
entered is valid.

I need the combo box to return the vendor number when the vendor name is
selected. I have achieved this by setting the text coulmn property to 2.
However, when I do this, the AutoWordSelect property now uses the vendor
number instead of the vendor name.

Here is exactly what I need:

Combo box is filled with the 2 columns of the vendor name and vendor number.
As you start typing the vendor name, it jumps to the vendor name that
matches what is being typed.
The combobox needs to be populated with the vendor number which is in coulmn
two fo the list fill range.

Have I confused anyone yet?

[witek]

jeffbert wrote:
> I am going to try to make this as clear as I can, as it is a little confusing
> to me. Here goes:
>
> I have an ActiveX combo box on my worksheet to select a Vendor. I have the
> Column Count property set to 2, so that I can see the Vendor name (which is
> in the first column of the fill range) and the vendor number (which is in the
> second coulmn of the fill range).
>
> I have the AutoWordSelect property set to True so that you can start typing
> a Vendor name and the combo box jumps to the vendor starting with the letters
> that you are typing. The MatchRequired property is set to True, ane the
> MatchEntry property is set to 1-Match Entry Complete to ensure that the value
> entered is valid.
>
> I need the combo box to return the vendor number when the vendor name is
> selected. I have achieved this by setting the text coulmn property to 2.
> However, when I do this, the AutoWordSelect property now uses the vendor
> number instead of the vendor name.
>
> Here is exactly what I need:
>
> Combo box is filled with the 2 columns of the vendor name and vendor number.
> As you start typing the vendor name, it jumps to the vendor name that
> matches what is being typed.
> The combobox needs to be populated with the vendor number which is in coulmn
> two fo the list fill range.
>
> Have I confused anyone yet?

Switch columns.
Put vendorID as first column
Set BoundColumn to 1
set ColumnCount to 2
set ColumnWidth to 0

(or 0;100 , each number represents width of one column)


if you need to read data from other then bound column

use
yourcombo.list (yourcombo.listindex, column)

be sure that something is selected and listindex is not -1

[jeffbert]

Thanks for the response, but it may be a little over my skill set. I know VBA
thru trial and error and the macro recorder. I assume you are having me put
the code into the combo box change event. So if this is correct, it should
look slomething like this? Where am I going wrong?

Private Sub ComboBox1_Change()
ComboBox1.list (ComboBox1.listindex, 1)


End Sub



"witek" wrote:

> jeffbert wrote:
> > I am going to try to make this as clear as I can, as it is a little confusing
> > to me. Here goes:
> >
> > I have an ActiveX combo box on my worksheet to select a Vendor. I have the
> > Column Count property set to 2, so that I can see the Vendor name (which is
> > in the first column of the fill range) and the vendor number (which is in the
> > second coulmn of the fill range).
> >
> > I have the AutoWordSelect property set to True so that you can start typing
> > a Vendor name and the combo box jumps to the vendor starting with the letters
> > that you are typing. The MatchRequired property is set to True, ane the
> > MatchEntry property is set to 1-Match Entry Complete to ensure that the value
> > entered is valid.
> >
> > I need the combo box to return the vendor number when the vendor name is
> > selected. I have achieved this by setting the text coulmn property to 2.
> > However, when I do this, the AutoWordSelect property now uses the vendor
> > number instead of the vendor name.
> >
> > Here is exactly what I need:
> >
> > Combo box is filled with the 2 columns of the vendor name and vendor number.
> > As you start typing the vendor name, it jumps to the vendor name that
> > matches what is being typed.
> > The combobox needs to be populated with the vendor number which is in coulmn
> > two fo the list fill range.
> >
> > Have I confused anyone yet?
>
> Switch columns.
> Put vendorID as first column
> Set BoundColumn to 1
> set ColumnCount to 2
> set ColumnWidth to 0
>
> (or 0;100 , each number represents width of one column)
>
>
> if you need to read data from other then bound column
>
> use
> yourcombo.list (yourcombo.listindex, column)
>
> be sure that something is selected and listindex is not -1
>
>
>
>
>
>
>

[witek]

Right.



Your bound column is a column nr 1 which is a column with text.
You need to read what is a value in second column.

Private Sub ComboBox1_Change()

dim id as integer

if combobox1.listindex <> -1 then
id = ComboBox1.list (ComboBox1.listindex, 1)
'do what you need

else
' nothing is selected
end if


End Sub


There are other solutions, but this one is the easiest one for you to
implement.





jeffbert wrote:
> Thanks for the response, but it may be a little over my skill set. I know VBA
> thru trial and error and the macro recorder. I assume you are having me put
> the code into the combo box change event. So if this is correct, it should
> look slomething like this? Where am I going wrong?
>
> Private Sub ComboBox1_Change()
> ComboBox1.list (ComboBox1.listindex, 1)
>
>
> End Sub
>
>
>
> "witek" wrote:
>
>
>>jeffbert wrote:
>>
>>>I am going to try to make this as clear as I can, as it is a little confusing
>>>to me. Here goes:
>>>
>>>I have an ActiveX combo box on my worksheet to select a Vendor. I have the
>>>Column Count property set to 2, so that I can see the Vendor name (which is
>>>in the first column of the fill range) and the vendor number (which is in the
>>>second coulmn of the fill range).
>>>
>>>I have the AutoWordSelect property set to True so that you can start typing
>>>a Vendor name and the combo box jumps to the vendor starting with the letters
>>>that you are typing. The MatchRequired property is set to True, ane the
>>>MatchEntry property is set to 1-Match Entry Complete to ensure that the value
>>>entered is valid.
>>>
>>>I need the combo box to return the vendor number when the vendor name is
>>>selected. I have achieved this by setting the text coulmn property to 2.
>>>However, when I do this, the AutoWordSelect property now uses the vendor
>>>number instead of the vendor name.
>>>
>>>Here is exactly what I need:
>>>
>>>Combo box is filled with the 2 columns of the vendor name and vendor number.
>>>As you start typing the vendor name, it jumps to the vendor name that
>>>matches what is being typed.
>>>The combobox needs to be populated with the vendor number which is in coulmn
>>>two fo the list fill range.
>>>
>>>Have I confused anyone yet?
>>
>>Switch columns.
>>Put vendorID as first column
>>Set BoundColumn to 1
>>set ColumnCount to 2
>>set ColumnWidth to 0
>>
>>(or 0;100 , each number represents width of one column)
>>
>>
>>if you need to read data from other then bound column
>>
>>use
>>yourcombo.list (yourcombo.listindex, column)
>>
>>be sure that something is selected and listindex is not -1
>>
>>
>>
>>
>>
>>
>>