colour format text when copied from text box
OK Try the below
Private Sub CommandButton2_Click()
Dim LastRow As Object
Dim DataStr As String
Dim na As Integer
Dim intPos As Integer
Set LastRow = Sheet1.Range("f4000").End(xlUp)
For na = 1 To 10
v = " "
If na = 6 Then v = " v "
DataStr = DataStr & v & _
StrConv(Me.Controls("TextBox" & na).Text, vbProperCase)
Next na
LastRow.Offset(0, -1).Value = Trim(DataStr)
'code inserted here
With lastRow.Offset(0, -1)
Do
intPos = InStr(intPos + 1, .Value, "v", vbTextCompare)
If intPos Then
..Characters(Start:=intPos, Length:=1).Font.FontStyle = "Bold"
..Characters(Start:=intPos, Length:=1).Font.ColorIndex = 3
End If
Loop Until intPos = 0
End With
'your old code starts here
For na = 1 To 10
Me.Controls("TextBox" & na).Text = ""
Next na
Me.Hide
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria
"Jock" wrote:
Hi Jacob, I forgot to mention that "Option Explicit" is at the top of this
code so I am getting "variable not defined" errors with your adaption.
--
Traa Dy Liooar
Jock
"Jacob Skaria" wrote:
Once the data is copied to the cell add the below code
LastRow.Offset(0, -1).Value = Trim(DataStr) 'copies text box data
''add new code here
With lastRow.Offset(0, -1)
Do
intPos = InStr(intPos + 1, .Value, "v", vbTextCompare)
If intPos Then
.Characters(Start:=intPos, Length:=1).Font.FontStyle = "Bold"
.Characters(Start:=intPos, Length:=1).Font.ColorIndex = 3
End If
Loop Until intPos = 0
End With
'continue with rest of your code
If this post helps click Yes
---------------
Jacob Skaria
"Jock" wrote:
Thanks to John for the code below which copies data from up to 10 text boxes
on a user form to a worksheet.
My query is can the lower case "v" be formatted to be in bold and coloured
red when copied to the worksheet?
Private Sub CommandButton2_Click()
Dim LastRow As Object
Dim DataStr As String
Dim na As Integer
Set LastRow = Sheet1.Range("f4000").End(xlUp)
For na = 1 To 10
v = " "
If na = 6 Then v = " v "
DataStr = DataStr & v & _
StrConv(Me.Controls("TextBox" & na).Text, _
vbProperCase)
Next na
LastRow.Offset(0, -1).Value = Trim(DataStr) 'copies text box data
to cell
For na = 1 To 10
Me.Controls("TextBox" & na).Text = ""
Next na
Me.Hide
End Sub
Thanks in advance
--
Traa Dy Liooar
Jock
|