colour format text when copied from text box

J

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
 
J

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
 
J

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.
 
J

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
 
J

Jock

Thank you very much. :)
--
Traa Dy Liooar

Jock


Jacob Skaria said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top