VBA userform output to Sheet

[zequla]

hi,

Is there a way to output txt field data and listbox data to a row in a sheet. I tryed it in my code below but it does not work. I use a userform in VBA where the user can isert data into txt fields and listbox fields. When the user clicks on save the data need to be loaded into a row in a sheet.

Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Input")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1).Value = Me.cbodate.Value
.Cells(lRow, 2).Value = Me.cboformule.Value
.Cells(lRow, 3).Value = Me.cboaccname.Value
.Cells(Row, 4).Value = Me.ListBox2.Value <--------------- this does not work??
.Cells(lRow, 5).Value = Me.txtopmkg.Value
.Cells(Row, 6).Value = Me.ListBox4.Value <--------------- this does not work??
.Cells(lRow, 7).Value = Me.txtotrstart.Value
.Cells(lRow, 8).Value = Me.txtexcstart.Value
.Cells(lRow, 9).Value = Me.txtvolstart.Value
.Cells(lRow, 10).Value = Me.txtnicostart.Value
.Cells(lRow, 11).Value = Me.txtlamstart.Value
.Cells(lRow, 12).Value = Me.txtfenstart.Value
.Cells(lRow, 13).Value = Me.txtotrvertrk.Value
.Cells(lRow, 14).Value = Me.txtvolvertrek.Value
.Cells(lRow, 15).Value = Me.txtexcevertrk.Value
.Cells(lRow, 16).Value = Me.txtnicovertrk.Value
.Cells(lRow, 17).Value = Me.txtlamivertrek.Value
.Cells(lRow, 18).Value = Me.txtfenvertrek.Value

End With

 

[zequla]

Alrddy tryed the list options instead of Value..

 

[rajkavikumar]

Hello Zequla,

I noticed that where you get error you have use "Row" instead of "lRow" and I didn't find any such variable declared by you that might be a cause.

[COLOR=#333333]'your code
[/COLOR][COLOR=#333333].Cells(Row, 6).Value = Me.ListBox4.Value 
[/COLOR][COLOR=#333333]'try this
[/COLOR][COLOR=#333333].Cells(lRow, 6).Value = Me.ListBox4.Value [/COLOR][COLOR=#333333]

[/COLOR]

 

[zequla]

hey raj ,

Ye i know but it still doesnt work..

 

[zequla]

The problem is that i want to output data from text fields AND listbox fields in the samen row..

 

[zequla]

bump

 

[rajkavikumar]

You can use loop to get data

For iCntr = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(iCntr) Then
        [COLOR=#333333].Cells(lRow, 6).Value =[/COLOR] Me.ListBox1.List(iCntr)
    End If
Next iCntr

 

[zequla]

Thanks Ray, i already found the solution. i transfered the listbox selected items to a textbox and output the textbox data to a cell.