formatting a text box with VBA

[sunilmulay]

Hi there

I have a text box on a sheet, and am trying to format it using VBA. I want to set the fill and font colours. I tried something like:

With shape("textbox20").font
.colour = XXXX
etc
End With

and

With shape("textbox20").interior
.colour = XXXX
etc
End With

....but I'm clearly not doing the right thing.....!

Can anyone show me how to do this?

BTW, the reason I want to use code, is that the formatting is conditional to certain criteria ie. conditional formatting on a text box.


Thanks in advance
Sunil

 

[gavinkelly]

Sub ColourBox()

    ActiveSheet.Shapes("TextBox20").Select
    With Selection.Font
        .ColorIndex = 32 'change to colour you want
    End With
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 54 'change to colour you want

End Sub

 

[xld]

    With ActiveSheet.OLEObjects("TextBox1")
    
        .Object.BackColor = RGB(255, 0, 0)
        .Object.ForeColor = RGB(0, 0, 255)
    End With

 

[sunilmulay]

Hi Guys

Thanks for your replies... I tried both versions, but the code doesn't recognize the object....

I've simply inserted a text box from scratch in Excel 2007. In the top left corner when I select the box, it says TextBox 20.

I would be happy to upload a sample file, but I don't know if that's possible in this forum....

thanks
Sunil

 

[pgc01]

Hi Sunil

How did you insert the textbox,

- Insert->TexBox

or

- Developper->Insert->ActiveX Controls->Textbox

?

 

[sunilmulay]

- Insert->TexBox
(in 2007 version)

Thanks
Sunil

 

[pgc01]

In that case a code like Gavin's should work. It's not necessary, however, to select the textbox.

You can use, for ex., something like:

' modify a textbox
Sub TxtBModify()
Dim txtB As TextBox
 
Set txtB = Worksheets("Sheet1").TextBoxes("MyTextBox")
With txtB
    .Interior.Color = vbYellow
    .Font.Color = vbBlue
End With
End Sub

Notice that in most cases it doesn't make much sense to use the automatic name of the textbox and it't better to use instead a name that has a meaning to you. You can change the name of the textbox when you create it. Ex.:

' create a textbox
Sub TxtBCreate()
Dim txtB As TextBox
 
Set txtB = Worksheets("Sheet1").TextBoxes.Add(100, 100, 200, 50)
txtB.Name = "MyTextBox"
End Sub

This makes the code easier to read when you refer to it later.

 

[xld]

    With ActiveSheet.Shapes("TextBox 1")
        
        .Fill.ForeColor.SchemeColor = 26
        With .TextFrame.Characters
        
            .Font.ColorIndex = 5
        End With
    End With

 

[sunilmulay]

Thanks I used the following:

  With ActiveSheet.Shapes("TextBox 1")
        
        .Fill.ForeColor.SchemeColor = 26
        With .TextFrame.Characters
        
            .Font.ColorIndex = 5
        End With
    End With

and it worked. Only thing now is I'm not sure how to establish what colour numbers the colours I've chosen are..... I tried using Record Macro and record myself applying the colours I want to a cell, and then I entered the colour numbers that the code generated, into the above code, but Excel didn't like that.....

Any idea why?

Thanks
S

 

[xld]

Here is an enumerated list which you can add to a module and then use the colour constant name.

Public Enum xlColorIndex
    xlCIBlack = 1
    xlCIWhite = 2
    xlCIRed = 3
    xlCIBrightGreen = 4
    xlCIBlue = 5
    xlCIYellow = 6
    xlCIPink = 7
    xlCITurquoise = 8
    xlCIDarkRed = 9
    xlCIGreen = 10
    xlCIDarkBlue = 11
    xlCIDarkYellow = 12
    xlCIViolet = 13
    xlCITeal = 14
    xlCIGray25 = 15
    xlCIGray50 = 16
    xlChartCIPeriwinkle = 17            '-----------------------------
    xlChartCIPlum = 18                  ' chart colours
    xlChartCIIvory = 19                 '
    xlChartCILightTurquoise = 20        '
    xlChartCIDarkPurple = 21            '
    xlChartCICoral = 22                 '
    xlChartCIOceanBlue = 23             '
    xlChartCIIceBlue = 24               '
    xlChartCIDarkBlue = 25              '
    xlChartCIPink = 26                  '
    xlChartCIYellow = 27                '
    xlChartCITurquoise = 28             '
    xlChartCIViolet = 29                '
    xlChartCIDarkRed = 30               '
    xlChartCITeal = 31                  '
    xlChartCIBlue = 32                  '-----------------------------
    xlCISkyBlue = 33
    xlCILightGreen = 35
    xlCILightYellow = 36
    xlCIPaleBlue = 37
    xlCIRose = 38
    xlCILavender = 39
    xlCITan = 40
    xlCILightBlue = 41
    xlCIAqua = 42
    xlCILime = 43
    xlCIGold = 44
    xlCILightOrange = 45
    xlCIOrange = 46
    xlCIBlueGray = 47
    xlCIGray40 = 48
    xlCIDarkTeal = 49
    xlCISeaGreen = 50
    xlCIDarkGreen = 51
    xlCIBrown = 53
    xlCIIndigo = 55
    xlCIGray80 = 56
End Enum