Copy Textbox values from UserForm to the worksheet

[sofas]

Welcome. I am trying to copy the textbox values from the user form to the worksheet. I have 25 textboxes. What I am trying to do is copy the values while emptying all the textbox elements after execution. I found this code for @DanteAmor by chance. No, but I am having a problem modifying it to match my application.

VBA to Populate an array with values from multiple Textboxes then outputting array to range in worksheet

Hi, I'm very new with Array in vba and find it hard to adapt the tutorials I find on webs to my task. I understand that array can store variables for later use. I want to populate a multi column array only with enabled textboxes values. I have 40 textboxes in frame1. If I check checkbox1...

www.mrexcel.com

Private Sub CommandButton1_Click()
Dim arr As Variant, j As Long
  Dim k As Long, m As Long, n As Long
  ReDim arr(1 To 10, 1 To 10)
 
  Set src = Sheets("Data")
  lastrow = src.Cells(src.Rows.Count, 1).End(xlUp).Row + 1
  n = 1
      k = k + 1
      m = 0
      For j = n To n + 10
        m = m + 1
        arr(k, m) = Controls("TextBox" & j)
      Next
    n = n + 10
   src.Range("B" & lastrow).Resize(k, 10).Value = arr
 With src.Range("A2:A" & src.Cells(src.Rows.Count, "B").End(xlUp).Row)
    .Value = Evaluate("ROW(" & .Address & ")")
  End With
End Sub

 

[AlphaFrog]

What are the names of your textboxes, and what cells do you want to put their values in? Be specific please.

 

[sofas]

First, thank you for your interest. Text boxes from Textbox 1 to Textbox 25. I want to copy the values starting from row 3. When I try again, the copy is done after the last row with data.

 

[AlphaFrog]

This writes to cells below the last used cell in column A

Private Sub CommandButton1_Click()
    
    Dim i As Long
    Dim LastCell As Range
    
    Set LastCell = Range("A" & Rows.Count).End(xlUp)
    
    For i = 1 To 25
        'Write textbox values to sheet
        LastCell.Offset(i).Value = Me.Controls("TextBox" & i).Value
        'Clear textbox value
        Me.Controls("TextBox" & i).Value = Empty
    Next i
            
End Sub

 

[sofas]

This writes to cells below the last used cell in column A

Private Sub CommandButton1_Click()
   
    Dim i As Long
    Dim LastCell As Range
   
    Set LastCell = Range("A" & Rows.Count).End(xlUp)
   
    For i = 1 To 25
        'Write textbox values to sheet
        LastCell.Offset(i).Value = Me.Controls("TextBox" & i).Value
        'Clear textbox value
        Me.Controls("TextBox" & i).Value = Empty
    Next i
           
End Sub

Thank you, this is exactly what was needed.

 

[sofas]

What are the names of your textboxes, and what cells do you want to put their values in? Be specific please.

Sorry, there is a problem that I did not notice before. Data must be copied horizontally. The suggested code is copied vertically
That is, from column A to W

 

[AlphaFrog]

Change this...

LastCell.Offset(i).Value = Me.Controls("TextBox" & i).Value

To this...

LastCell.Offset(1, i - 1).Value = Me.Controls("TextBox" & i).Value