Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple colours in a Textbox on a Form.
Here is what I am trying to achieve.
First I do some searching on a sheet to get to the activecell that I want. I then go down through 10 cells and the text in each cell gets added to a variable (z). z now contains a string of text which consists of all of the text data in 10 vertical cells all added together (A1&A2&A3...0 for example). I then want to put this into a text box which will display it a lot neater than having to look at a spreadsheet. NOW, when searching there will sometimes be text in RED, and if this is the case then I want that to be red in the textbox. So in the text box I want black text but if some text was red I want that to be displayed in the text box as red, along with the black text. I think something I want is (part english, part VB): z = ActiveCell.Value For x = 1 To 10 ActiveCell.Offset(1, 0).Select If ActiveCell.Font.Color = RGB(255, 0, 0) Then Z=Z+vbCrLf +vbCrLf +ACTIVECELL.VALUE (AS RED TEXT) : GOTO 555 z = z + vbCrLf + vbCrLf + ActiveCell.Value 555 Next x TextBox4.Value = z This would then result in some text being black, and some of it red in a textbox If I was writing this in BASIC then it would be very easy to do but my unfamiliarity with Excel's VB makes it a little more challenging. Any help appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple colours in a Textbox on a Form.
The following code seems a bit long-winded but at least it works.
The example is for text in cells A1,B1,C1 and D1 i.e. Row 1, Columns 1 to 4 Sub TxtFontColor() Dim Var, X As Integer, Ln(1 To 4) As Integer Dim Cindx(1 To 4) As Integer Dim St As Integer, Lgth As Integer 'record the ColorIndex and string length of each cell's content For X = 1 To 4 Cindx(X) = Cells(1, X).Font.ColorIndex Ln(X) = Len(Cells(1, X)) 'concatenate Var = Var & Cells(1, X) Next 'post to a textbox ActiveSheet.Shapes("Text Box 1").Select Selection.Text = Var 'use recorded data to format textbox string St = 1: Lgth = 0 For X = 1 To 4 Lgth = Ln(X) With Selection.Characters(start:=St, Length:=Lgth).Font .ColorIndex = Cindx(X) End With St = St + Ln(X) Next End Sub regards' Don -- "Spaceman" wrote in message ... Here is what I am trying to achieve. First I do some searching on a sheet to get to the activecell that I want. I then go down through 10 cells and the text in each cell gets added to a variable (z). z now contains a string of text which consists of all of the text data in 10 vertical cells all added together (A1&A2&A3...0 for example). I then want to put this into a text box which will display it a lot neater than having to look at a spreadsheet. NOW, when searching there will sometimes be text in RED, and if this is the case then I want that to be red in the textbox. So in the text box I want black text but if some text was red I want that to be displayed in the text box as red, along with the black text. I think something I want is (part english, part VB): z = ActiveCell.Value For x = 1 To 10 ActiveCell.Offset(1, 0).Select If ActiveCell.Font.Color = RGB(255, 0, 0) Then Z=Z+vbCrLf +vbCrLf +ACTIVECELL.VALUE (AS RED TEXT) : GOTO 555 z = z + vbCrLf + vbCrLf + ActiveCell.Value 555 Next x TextBox4.Value = z This would then result in some text being black, and some of it red in a textbox If I was writing this in BASIC then it would be very easy to do but my unfamiliarity with Excel's VB makes it a little more challenging. Any help appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set Textbox on a Form to Deafault | New Users to Excel | |||
Text Size in Label or Textbox in Form | Excel Discussion (Misc queries) | |||
end down option in a textbox of a form | Excel Discussion (Misc queries) | |||
User Form in VB = TextBox | Excel Discussion (Misc queries) | |||
User Form textbox formatting | Excel Programming |