Combobox selection

[Greg B]

Hi All,
I am hoping for help with a bit of code, I want to use a combobox on a
userform to be used as a selector for parts. The spreadsheet is called
"Stock" It looks for the stock number is column A and I would like it to
show the name of the part in a textbox. I have the basic code to fill the
combobox but I am not sure how to use the selection using the
combobox.change method?
i.e.

PartNumber (combobox) Item Desc Textbox

1231 USB Cable

Thanks in Advance
Greg

 

[GS]

Greg B presented the following explanation :

Hi All,
I am hoping for help with a bit of code, I want to use a combobox on a
userform to be used as a selector for parts. The spreadsheet is called
"Stock" It looks for the stock number is column A and I would like it to show
the name of the part in a textbox. I have the basic code to fill the combobox
but I am not sure how to use the selection using the combobox.change method?
i.e.

PartNumber (combobox) Item Desc Textbox

1231 USB Cable

Thanks in Advance
Greg

You could use a label for the description, unless you want users to be
able to edit the description.

You can put all the part data into the combobox using the appropriate
'ColumnCount' so it contains the same values as your spreadsheet. Then
your textbox can hold the column that contains the description of the
selected item. You can hide any columns you don't want displayed by
setting 'ColumnWidths' so those columns are zero width. Also, you can
set the 'BoundColumn' to the column that holds the description and
that's what gets returned when you access the 'Value'.

Example:

In the Combobox1_Change event...
TextBox1.Text = ComboBox1.Value

...where BoundColumn is set to 2 if that's where the description is:

With ComboBox1
.AddItem "PartNumber1"
.List(0, 1) = "Part Number 1 Description"
End With

So if your spreadsheet list has part numbers in ColA and descriptions
in ColB then you'll want to dump those two cols into an array and put
that as the 'List' instead of just part numbers. Set the ColumnWidths
property to 100,0 or whatever width will display the part number
adequately for the 1st col's width. The 2nd value hides the description
column but that's the 'BoundColumn' and so is what gets returned as its
'Value' when selections are made.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion