posting my data from userform to sheet with the same format table

[abdelfattah]

i have userform contains 3 textbox ans sheet1 contains data 3 columns and have table in sheet1
here i have code transfer data from userform to sheet1 but my problem every time when i fill data in userform and transfer data not the same format the next row what filled

Private Sub CommandButton1_Click()On Error Resume Next
Dim My_sh As Worksheet
Set My_sh = Worksheets("sheet1")
Dim lastrow As Integer
Dim i%
With My_sh
lastrow = .Cells(Rows.Count, 1).End(3).Row + 1
For i = 1 To 3
.Cells(lastrow, i).Value = Me.Controls("TextBox" & i)
Me.Controls("TextBox" & i) = ""
Next
MsgBox "done"
End With
End Sub

 

[DanteAmor]

You must put the type of data when you pass it to the sheet, for example:

Private Sub CommandButton1_Click()
  On Error Resume Next
  Dim My_sh As Worksheet
  Set My_sh = Worksheets("sheet1")
  Dim lastrow As Integer
  Dim i%
  With My_sh
    lastrow = .Cells(Rows.Count, 1).End(3).Row + 1
    .Cells(lastrow, "A").Value = TextBox1.Value           'text value
    .Cells(lastrow, "B").Value = CDate(TextBox2.Value)    'date value
    .Cells(lastrow, "C").Value = CDbl(TextBox3.Value)     'number value
    MsgBox "done"
  End With
End Sub

 

[abdelfattah]

You must put the type of data when you pass it to the sheet, for example:

Private Sub CommandButton1_Click()
  On Error Resume Next
  Dim My_sh As Worksheet
  Set My_sh = Worksheets("sheet1")
  Dim lastrow As Integer
  Dim i%
  With My_sh
    lastrow = .Cells(Rows.Count, 1).End(3).Row + 1
    .Cells(lastrow, "A").Value = TextBox1.Value           'text value
    .Cells(lastrow, "B").Value = CDate(TextBox2.Value)    'date value
    .Cells(lastrow, "C").Value = CDbl(TextBox3.Value)     'number value
    MsgBox "done"
  End With
End Sub

thanks for your replying but you don't understand me your code and mine it works but the problem the format the table in sheet for instance i have color blue in my table in sheet the data begin from row3 i would when i fill data in userform and transfer data automatically insert the colored row with the data like the row what before

 

[DanteAmor]

Then before putting the data you should copy the format of the previous row and paste in the new row.
For example:

Private Sub CommandButton1_Click()
  On Error Resume Next
  Dim My_sh As Worksheet
  Set My_sh = Worksheets("sheet1")
  Dim lastrow As Integer
  Dim i%
  Application.ScreenUpdating = False
  With My_sh
    lastrow = .Cells(Rows.Count, 1).End(3).Row + 1
[COLOR=#0000ff]    .Range(.Cells(lastrow - 1, 1), .Cells(lastrow - 1, 3)).Copy[/COLOR]
[COLOR=#0000ff]    .Cells(lastrow, 1).PasteSpecial Paste:=xlPasteFormats[/COLOR]
    For i = 1 To 3
      .Cells(lastrow, i).Value = Me.Controls("TextBox" & i)
      Me.Controls("TextBox" & i) = ""
    Next
  End With
  Application.ScreenUpdating = True
  Application.CutCopyMode = False
  MsgBox "done"
End Sub

 

[abdelfattah]

Then before putting the data you should copy the format of the previous row and paste in the new row.
For example:

Private Sub CommandButton1_Click()
  On Error Resume Next
  Dim My_sh As Worksheet
  Set My_sh = Worksheets("sheet1")
  Dim lastrow As Integer
  Dim i%
  Application.ScreenUpdating = False
  With My_sh
    lastrow = .Cells(Rows.Count, 1).End(3).Row + 1
[COLOR=#0000ff]    .Range(.Cells(lastrow - 1, 1), .Cells(lastrow - 1, 3)).Copy[/COLOR]
[COLOR=#0000ff]    .Cells(lastrow, 1).PasteSpecial Paste:=xlPasteFormats[/COLOR]
    For i = 1 To 3
      .Cells(lastrow, i).Value = Me.Controls("TextBox" & i)
      Me.Controls("TextBox" & i) = ""
    Next
  End With
  Application.ScreenUpdating = True
  Application.CutCopyMode = False
  MsgBox "done"
End Sub

thanks so much the code is worked

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.