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
    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 
    
    
    Formatting tags added by mark007

  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,312
    Location
    Function FindLastRow(ByVal ws As Worksheet, ColumnLetter As String) As Long 
        FindLastRow = ws.Range(ColumnLetter & Rows.Count).End(xlUp).Row 
    End Function 
    
    
    Formatting tags added by mark007
    Slick. I like.
    Please take the time to read the Forum FAQ

  5. #5
    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 
    
    
    Formatting tags added by mark007

  7. #7
    Instead of
    ws.Cells(nextrows, 2) = ctrl.Value 
    
    
    Formatting tags added by mark007
    use
    ws.Cells(nextrows, 2) = ctrl.Caption 
    
    
    Formatting tags added by mark007

Posting Permissions

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