colour format text when copied from text box

[Jock]

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

 

[Jacob Skaria]

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

 

[Jock]

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.

 

[Jacob Skaria]

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

 

[Jock]

Thank you very much.
--
Traa Dy Liooar

Jock

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