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

[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