Transfer data from userform to sheet with the same format of table

Posts
26
Registration date
Thursday July 18, 2019
Status
Member
Last seen
November 19, 2019
- - Latest reply: ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
- Aug 28, 2019 at 02:28 PM
hi, experts
i have userform contains 3 textbox and sheet1 contains table i designed from 3 columns i would every time when fill data in userform automatically insert row the same format which before it (the same color ,bold...etc)
this is my 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

See more 

2 replies

Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1568
0
Thank you
Do you have a question?
abdelfatah_0230
Posts
26
Registration date
Thursday July 18, 2019
Status
Member
Last seen
November 19, 2019
-
i would code transfer data from user form to sheet with the same format of the designed table in sheet1 every time i fill data in userform my code is above
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1568 -
Ok, so what is the issue? What are you asking? Are asking will my code work?
abdelfatah_0230
Posts
26
Registration date
Thursday July 18, 2019
Status
Member
Last seen
November 19, 2019
-
my code does work but, what i need add adjusting to become the same format my table
for instance the data begin from row3 and contain blue color and the line is 14 size and bold here when i transfer data every time i would automatically insert the same format row
abdelfatah_0230
Posts
26
Registration date
Thursday July 18, 2019
Status
Member
Last seen
November 19, 2019
-
unfortinatlly you don't understand the result what i would in the second image

when i transfer data it give me this



and what i would this



you can note the color in the item 2
i need adjusting the code above but i no know how
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1568 -
You test the row above, and load the result into a variable as in:

Thefontsize=cells (x,1).font.size
Cells (x + 1,1).font.size = Thefontsize

If you need to test for bold, it would be a true false.

So, a bold font would be
Isitbold = cells (x,1).font.bold
Cells (x+1,1).font.bold = Isitbold
Respond to ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1568
0
Thank you
If you need to get the format for a cell, it would be

Cells (1,1). Font.colorindex

Load variable with the row that you wish to test, then place it back on the current row.

If you wish to test font size, it is

Font.size

I hope this is what you are asking.


Respond to ac3mark