Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour format text when copied from text box
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour format text when copied from text box
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour format text when copied from text box
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour format text when copied from text box
Thank you very much. :)
-- Traa Dy Liooar Jock "Jacob Skaria" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text format - setting text colour with code | Excel Discussion (Misc queries) | |||
Conditionl format a cell to change text colour | Excel Discussion (Misc queries) | |||
Conditional Format to Retain Text Colour from a List on another Sh | New Users to Excel | |||
Can i format (colour) selective text in a cell using excel2000? | Excel Discussion (Misc queries) | |||
Ensuring a cell is copied in text format | Excel Programming |