Results 1 to 6 of 6

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

  1. #1
    Board Regular
    Join Date
    May 2019
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 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
    For i = 1 To 3
    .Cells(lastrow, i).Value = Me.Controls("TextBox" & i)
    Me.Controls("TextBox" & i) = ""
    Next
    MsgBox "done"
    End With
    End Sub

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,250
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

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

    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
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    May 2019
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by DanteAmor View Post
    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

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,250
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

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

    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
        .Range(.Cells(lastrow - 1, 1), .Cells(lastrow - 1, 3)).Copy
        .Cells(lastrow, 1).PasteSpecial Paste:=xlPasteFormats
        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
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    May 2019
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by DanteAmor View Post
    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
        .Range(.Cells(lastrow - 1, 1), .Cells(lastrow - 1, 3)).Copy
        .Cells(lastrow, 1).PasteSpecial Paste:=xlPasteFormats
        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

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,250
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

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

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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