Consulting

Results 1 to 7 of 7

Thread: Transferring Userform data to excel

  1. #1

    Transferring Userform data to excel

    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

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    696
    Location
    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

  3. #3
    Wow! That worked perfectly! Thanks soooo much

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,671
    Location
    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.
    Please take the time to read the Forum FAQ

  5. #5
    VBAX Expert
    Joined
    Aug 2004
    Posts
    696
    Location
    Thanks, Sam.
    i like it too

  6. #6
    Quote Originally Posted by JKwan View Post
    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

  7. #7
    VBAX Expert
    Joined
    Aug 2004
    Posts
    696
    Location
    Instead of
    ws.Cells(nextrows, 2) = ctrl.Value
    use
    ws.Cells(nextrows, 2) = ctrl.Caption

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •