Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021
 -
 Blocked Profile -
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

Answer 1 / 2

Do you have a question?
Report
Posts
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021
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
Report
Blocked Profile
Ok, so what is the issue? What are you asking? Are asking will my code work?
Report
Posts
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021
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
Report
Posts
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021
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
Report
Blocked Profile
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
Answer 2 / 2

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.


Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!