Transferring Userform data to excel

[trishgyrl]

Hi. Please help.

I am trying to use code that says for every textbox control where visible property is true, then, transfer the value in the textbox to rows in Column A of Sheet2 in Excel. Can someone please let me know where I am going wrong?


If bComplete Then

Dim nextrow As Long
Dim ctrl As Control

Sheets("sheet2").Activate

'determine the next empty row
nextrow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

'transfer the control values

For Each ctrl In Me.Controls
If ctrl.Visible = True Then

If TypeName(ctrl) = "TextBox" Then

Cells(nextrow + 1, 1) = ctrl.Value

End If
End If
Next ctrl


Unload Me

End If

[JKwan]

try this:

Private Sub CommandButton1_Click()
Dim nextrow As Long
Dim ctrl As Control

Dim ws As Worksheet

If bcomplete Then

Set ws = Worksheets("Sheet2")
ws.Select

'determine the next empty row
nextrow = FindLastRow(ws, "A") + 1

'transfer the control values

For Each ctrl In Me.Controls
If ctrl.Visible Then

If TypeName(ctrl) = "TextBox" Then

ws.Cells(nextrow, 1) = ctrl.Value
nextrow = nextrow + 1
End If
End If
Next ctrl

Unload Me
End If
End Sub
Function FindLastRow(ByVal ws As Worksheet, ColumnLetter As String) As Long
FindLastRow = ws.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function

[trishgyrl]

Wow! That worked perfectly! Thanks soooo much :clap:

[SamT]

Function FindLastRow(ByVal ws As Worksheet, ColumnLetter As String) As Long
FindLastRow = ws.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function
Slick. I like.

[JKwan]

Thanks, Sam.
i like it too

[trishgyrl]

Thanks, Sam.
i like it too

If I want to add the caption of each visible label to the worksheet, as well, how would I go about that?

I would like the caption of each visible label to show in rows of column A and the value of the textbox to go in rows of column B.

I added another variable but, I'm getting an error that say "object does not support this property or method." Please help. Thanks in advance.




Dim nextrow As Long
Dim nextrows As Long
Dim ctrl As Control

Dim ws As Worksheet

If bComplete Then

Set ws = Worksheets("Sheet2")
ws.Select

'determine the next empty row
nextrow = FindLastRow(ws, "A") + 1
nextrows = FindLastRow(ws, "B") + 1

'transfer the control values

For Each ctrl In Me.Controls
If ctrl.Visible Then

If TypeName(ctrl) = "TextBox" Then

ws.Cells(nextrow, 1) = ctrl.Value
nextrow = nextrow + 1
Else

If TypeName(ctrl) = "Label" Then

ws.Cells(nextrows, 2) = ctrl.Value
nextrows = nextrows + 1
End If
End If
End If
Next ctrl



Unload Me
End If

[JKwan]

Instead of

ws.Cells(nextrows, 2) = ctrl.Value

use

ws.Cells(nextrows, 2) = ctrl.Caption