Multiple colours in a Textbox on a Form.

  • Thread starter Thread starter Spaceman
  • Start date Start date
S

Spaceman

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.
 
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
 
Back
Top