Transfer data from userform textboxes using the same row

Bendfella · Mar 12th 2020

hi everyone

I am busy with a project and will take it step by step as it goes.

for now I need help.

I have a userform with 4 textboxes. when I type in the dates and click on transfer, it transfers the data to the excel sheet correctly. Cells A1 to D4.

however, I just want to use row 2 with columns A,B,C,D because those values is linked to a word document.

I do not know how to make it not go to rows 3 and 4 and 5 etc

**royUK** · Mar 12th 2020

If you want to always use Row2 then change

Code

'find first empty row in database
'find first empty row in database
iRow = ws.Cells.Find(what:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

to

Code

iRow=2

I would actually not use word, but format the worksheet correctly to print - much simpler

Justin Doward · Mar 13th 2020

If you want to keep pushing the other records down rather than writing over them insert a row:

Code

Private Sub CmdButton_add_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
'insert and identify print row
ws.Rows(2).EntireRow.Insert
iRow = 2
'copy data to sheet
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
ws.Cells(iRow, 3).Value = Me.TextBox3.Value
ws.Cells(iRow, 4).Value = Me.TextBox4.Value
End Sub

Display More

Bendfella · Mar 16th 2020

Thank you it worked!!!!

**royUK** · Mar 16th 2020

Which suggestion did you use?

Bendfella · Mar 16th 2020

Code

Worksheets(TargetSheet).Activate
ActiveSheet.Range("D8").Value = TextBox1.Value
ActiveSheet.Range("D9").Value = TextBox2.Value
ActiveSheet.Range("D10").Value = TextBox3.Value
ActiveSheet.Range("D11").Value = TextBox4.Value
ActiveSheet.Range("D12").Value = TextBox5.Value
ActiveSheet.Range("D13").Value = TextBox6.Value
ActiveSheet.Range("D14").Value = TextBox7.Value
ActiveSheet.Range("D15").Value = TextBox8.Value
'Balance Face Values at the end of the day
ActiveSheet.Range("K20").Value = TextBox9.Value
ActiveSheet.Range("K21").Value = TextBox10.Value
ActiveSheet.Range("K22").Value = TextBox11.Value
ActiveSheet.Range("K23").Value = TextBox12.Value
'Total amount of Face Values Used
ActiveSheet.Range("M24").Value = TextBox13.Value
End Sub

Display More

**royUK** · Mar 16th 2020

You don't need to activate the sheet, just define it in the code. You can also loop to load the write the data. Try

Code

Dim iX As Integer
With Worksheets(TargetSheet)
For iX = 1 To 8
.Range("D" & iX + 7).Value = Me("TextBox" & iX).Value
Next iX
For iX = 9 To 12
'Balance Face Values at the end of the day
.Range("K" & iX + 11).Value = Me("TextBox" & iX).Value
End With
'Total amount of Face Values Used
ActiveSheet.Range("M24").Value = TextBox13.Value
End With

Display More

Bendfella · Mar 17th 2020

Thank you Roy. It worked like a charm!!!

**royUK** · Mar 17th 2020

Pleased to help

Share