combobox and 2nd value needed

[scott micklo]

Hi all

I have a combobox on my userform that has 2 columns. The combobox has the name cbPartno.

The first column is the part number and the second column is the part description. They are read in from a data base. All is go there.

My question is:

I can get the part number (from column 1) into a variable for use. But how do I get the text description out of the second column and put it in a variable?

I have tried several codes that give me errors.


With Sheets("Sheet1")
.Cells(foundTotals.Row - 2, 1).Value = Me.cbPartno(1).Value


set description = Me.cbPartno(1).value
description = Me.cbPartno(1).value


Thanks

[DBY]

Hello
Maybe something like:

Please Login or Register  to view this content.

*Note column index starts at 0.

DBY

[mikerickson]

Two ways to do this.
1) read from the list

Please Login or Register  to view this content.

2) use the TextColumn and BoundColumn properties. Which column the .Text value of the Combobox comes from is determined by the .TextColumn property and the column for the .Value property is determined by the .BoundColumn. NOTE: What you see in the combobox when there is no dropdown is the .Text value of the combobox, not the .Value.

Please Login or Register  to view this content.

[scott micklo]

OK Thanks.

So here is what I have:

combo box first column (column(0)) is part number second column (column(1)) is description

I wish to take the description ( the portion from the second column of the combobox) and put it into a variable for use throughout my program.

When i use your .textcolumn and boundcolumn properties I get run time errors.

So, how would I go about taking the description and putting it into a variable for use?

[bakerman2]

Please Login or Register  to view this content.

[scott micklo]

Using the code you gave me here are the errors I get:

If I put in the .textcolumn and boundcolum in the initalize procedure,

I get an error in the "click" procedure where I am displaying both colums. Code:

Please Login or Register  to view this content.

If I take out the .text and .bound, it work ok, but then I get the same error 381 could not get the list property invalid property array index

Please Login or Register  to view this content.

I have tried in in the "click" initalize and command button procedures.

Maybe this will help a bit. Here is the initalize procedure and how the colums are being read in:

Please Login or Register  to view this content.

Sorry for teh novie questions and issues. Still learning the VBA programming.

Thanks so much

[bakerman2]

Made you simple example to show how it works.
Select value in combobox and then msgbox displays value from second column.

[mikerickson]

Please Login or Register  to view this content.

This looks like you are trying to set the .Value of the combox to a value that isn't in the combobox.

Please Login or Register  to view this content.

This will error if the .ListIndex = -1, i.e. if there is nothing selected from the combobox.

Please Login or Register  to view this content.

This can be replaced with the non-looping

Please Login or Register  to view this content.

[scott micklo]

Made you simple example to show how it works.
Select value in combobox and then msgbox displays value from second column.

Your code work great in your example. Unfortunately, in my code, it fails at the line
Partno = .list(.ListIndex, 0) ' where I am attempting to take the part number (whats in the first column) and put it invariable Partno for use later in program.

I keep getting Runtime error 381. Could not get the List property Invalid property array index.

I'm attaching the file with code so you can see. I don't think anything would be changing and causing it to fail.

It may be that I am not familiar enough with VBA coding and the code for .ListIndex, 0 is the issue.


Thanks

[mikerickson]

I notice a couple of problems with the attached file.
First off, there is a Workbook_Open event in the user form HeaderInfo code module, which is never called.

More to the point of this thread, in the other user form

Please Login or Register  to view this content.

The variables Partno and description are scoped only to that procedure, which is why their value is vbNullString when referred to outside of that procedure. They need to be scoped to be module wide variables, by putting their declaration lines before any procedure and after the (missing) Option
Explicit line.

Please Login or Register  to view this content.

[bakerman2]

Made a few changes to show how it works.
Open userform, select partNo and click Messagebox button.

[scott micklo]

OK. It looks good and I understand how it works.

I noticed that you commented out the part about loading the data for teh drop down menu from the data base on our network drive.
I tried to adapt your code and add those back in, but I keep getting either run time or complie errors (subscript...)

So how would I incorporate your code for the getting of the data from the drop down menu and use of the data base on the network drive
to fill it? Use of the network drive is important as it is where all the parts get added when we add or revise parts.

thanks

[bakerman2]

I just commented that out to get it working for me.
If your parts database really starts at row 883 then this works just fine.

Please Login or Register  to view this content.