Previous Topic Next Topic

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

Closed
abdelfatah_0230
Posts
73
Registration date
Thursday July 18, 2019
Status
Member
Last seen
July 23, 2022
 - Aug 28, 2019 at 11:34 AM
 Blocked Profile - 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

2 replies

Answer 1 / 2
Blocked Profile
Aug 28, 2019 at 11:51 AM
Do you have a question?
0
abdelfatah_0230
Posts
73
Registration date
Thursday July 18, 2019
Status
Member
Last seen
July 23, 2022
Aug 28, 2019 at 12:06 PM
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
0
Blocked Profile
Aug 28, 2019 at 12:07 PM
Ok, so what is the issue? What are you asking? Are asking will my code work?
0
abdelfatah_0230
Posts
73
Registration date
Thursday July 18, 2019
Status
Member
Last seen
July 23, 2022
Updated on Aug 28, 2019 at 12:18 PM
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
0
abdelfatah_0230
Posts
73
Registration date
Thursday July 18, 2019
Status
Member
Last seen
July 23, 2022
Aug 28, 2019 at 02:09 PM
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
0
Blocked Profile
Updated on Aug 28, 2019 at 02:40 PM
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
0
Answer 2 / 2
Blocked Profile
Aug 28, 2019 at 01:42 PM
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.


0