I'm having a small problem with writing data from a user form to a
worksheet. The user form reads data stored in a separate worksheet but
in the same workbook as to what I want to write to. The data is a set
of constants that will be used later for a calculation. There should be
a screen capture below:

4322

The user form is split into two columns in a list box and the data is
read in from RowSource in the properties box of the list box (i.e.
sheet2!a2..b25).

I'm using the following line of code to write the data from the list
box to cell A1 on sheet1:

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

This all works fine; however my problem is that the code is writing
data to cell A1 from the first column in my user form and I require the
numerical value in the second column . The first column is
simply there for the users information.

I'm assuming there is a command in VBA that will allow me to do this
but I can't figure it out.

Any help would be greatly appreciated. :)

Best Regards,

Aaron


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

--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=508754

from help on the BoundColumn Property

When the user chooses a row in a multicolumn ListBox or ComboBox, the
BoundColumn property identifies which item from that row to store as the
value of the control.

So set the boundcolumn property to 2 and

Cells(1, 1).Value = lstGradeAndSMYS.Value

for completeness, from the help on Textcolumn property:

When the user selects a row from a ComboBox or ListBox, the column
referenced by TextColumn is stored in the Text property



--
Regards,
Tom Ogilvy


"Aaron1978" wrote
in message ...

I'm having a small problem with writing data from a user form to a
worksheet. The user form reads data stored in a separate worksheet but
in the same workbook as to what I want to write to. The data is a set
of constants that will be used later for a calculation. There should be
a screen capture below:

4322

The user form is split into two columns in a list box and the data is
read in from RowSource in the properties box of the list box (i.e.
sheet2!a2..b25).

I'm using the following line of code to write the data from the list
box to cell A1 on sheet1:

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

This all works fine; however my problem is that the code is writing
data to cell A1 from the first column in my user form and I require the
numerical value in the second column . The first column is
simply there for the users information.

I'm assuming there is a command in VBA that will allow me to do this
but I can't figure it out.

Any help would be greatly appreciated. :)

Best Regards,

Aaron


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

--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile:
http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=508754

Excellent. That worked perfectly. Thanks.:)


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=508754