Reading Data from a User Form.

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:

UserForm.jpg

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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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.