Reading data from a list box

[Aaron1978]

Hi all,

I have a simple list box with two columns. The data for the columns are
read in from a worksheet within the workbook.

4421

When the user selects which steel grade they wish to use and then
clicks add; the data from the table is read to a spread sheet cell via
the following piece of code:

Private Sub subPutData()
Sheets("Sheet1").Select
Cells(8, 3).Value = lstGradeAndSMYS.Value
End Sub

This reads in the numerical value from the list box (i.e. SMYS, i.e.
the second column). What I would also like to be able to do is read in
the Steel Grade (i.e. the first column) and write that to a different
cell. I guess what I don't understand is why the above code chooses the
second column over the first column to write the data to Cell(8,3).

Any help would be greatly appreiated.

Best Regards,

Aaron


+-------------------------------------------------------------------+
|Filename: table.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4421 |
+-------------------------------------------------------------------+

 

[Jim Cone]

Aaron,

The "LinkedCell" displays the "Value" of the ListBox.
The "Bound" column specifies the column used for the value.
Look in the properties window of the ListBox.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Aaron1978" wrote in message...
Hi all,
I have a simple list box with two columns. The data for the columns are
read in from a worksheet within the workbook.
4421
When the user selects which steel grade they wish to use and then
clicks add; the data from the table is read to a spread sheet cell via
the following piece of code:

Private Sub subPutData()
Sheets("Sheet1").Select
Cells(8, 3).Value = lstGradeAndSMYS.Value
End Sub

This reads in the numerical value from the list box (i.e. SMYS, i.e.
the second column). What I would also like to be able to do is read in
the Steel Grade (i.e. the first column) and write that to a different
cell. I guess what I don't understand is why the above code chooses the
second column over the first column to write the data to Cell(8,3).
Any help would be greatly appreiated.
Best Regards,
Aaron

 

[Aaron1978]

Thanks. That makes a bit more sense now. What I would like to be able to
do is to read in data from both columns to two different cells. i.e. the
user needs to have both sets of data on their worksheet before they can
proceed. At the moment I can only read in one piece of data i.e. that
data which has a bound column.

Best Regards,

Aaron

 

[Jim Cone]

Aaron,

You can use the "Column" property of a listbox along with the "ListIndex"
property to specify values. Both properties start with zero, so the second
column is 1...

x = ListBox1.Column(1, ListBox1.ListIndex)

When nothing is selected, ListIndex is -1.

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Aaron1978" wrote in message...
Thanks. That makes a bit more sense now. What I would like to be able to
do is to read in data from both columns to two different cells. i.e. the
user needs to have both sets of data on their worksheet before they can
proceed. At the moment I can only read in one piece of data i.e. that
data which has a bound column.
Best Regards,
Aaron

 

[Aaron1978]

Thanks again, however I'm still getting an error. I've used two lines of
code to write the values contained in the list box to the spread sheet:

Cells(9, 3).Value = lstGradeAndSMYS.Column(1,
lstGradeAndSMYS.ListIndex)
Cells(8, 3).Value = lstGradeAndSMYS.Column(2,
lstGradeAndSMYS.ListIndex)

Excel if happy with the first line of code but it doesn't like the
second. I get an error saying "Runtime Error....Could not get the
column property. Invalid Argument".

Any ideas what is going wrng here?

Best Regards,

Aaron

 

[Jim Cone]

Aaron,
How many columns are there in your listbox?
".Column(2," is actually column 3.
Take another look at my last message.
Jim Cone


"Aaron1978" wrote in message...
Thanks again, however I'm still getting an error. I've used two lines of
code to write the values contained in the list box to the spread sheet:

Cells(9, 3).Value = lstGradeAndSMYS.Column(1,
lstGradeAndSMYS.ListIndex)
Cells(8, 3).Value = lstGradeAndSMYS.Column(2,
lstGradeAndSMYS.ListIndex)

Excel if happy with the first line of code but it doesn't like the
second. I get an error saying "Runtime Error....Could not get the
column property. Invalid Argument".

Any ideas what is going wrng here?
Best Regards,
Aaron

 

[Aaron1978]

Ahhhhhh I see. The penny's dropped. Thanks for all your help. Much
appreciated.