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

abdelfattah

Board Regular
Joined
May 3, 2019
Messages
149
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
HTML:
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,747
Office Version
2007
Platform
Windows
You must put the type of data when you pass it to the sheet, for example:


Code:
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

Board Regular
Joined
May 3, 2019
Messages
149
You must put the type of data when you pass it to the sheet, for example:


Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,747
Office Version
2007
Platform
Windows
Then before putting the data you should copy the format of the previous row and paste in the new row.
For example:

Code:
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

Board Regular
Joined
May 3, 2019
Messages
149
Then before putting the data you should copy the format of the previous row and paste in the new row.
For example:

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,747
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,086,078
Messages
5,387,686
Members
402,074
Latest member
Ugly Jake

Some videos you may like

This Week's Hot Topics

Top