Textbox formatting question

[dfinkels]

I have a simple macro that places a blue circle on a cell on my excel sheet.

I want to take the value of a cell, say E2, and place it on top of the circle, so you can see the value "in/on" the circle

I've written the following to create a text box with the value from cell E2 on top of the circle.

Sub Draw_Circle3()
Dim Diam As Single
Set myDocument = Worksheets(1)
Diam = Range("e2") 'pulls in volume from E2 in worksheet

myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 150, 175) _
.TextFrame.Characters.Text = Diam
End Sub


However, I cannot figure out to do the following: format the text box so it is transparent (I want to see circle behind it), no border, text is centered vertically and horizontally, font is bold, and font color is white.

Any ideas?

[Bob Phillips]

Dim Diam As Single
Dim myDocument As Worksheet
Dim shp As Shape

Set myDocument = Worksheets(1)
Diam = Range("E2").Value 'pulls in volume from E2 in worksheet

Set shp = myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 150, 175)
With shp

.TextFrame.Characters.Text = Diam
.Fill.Visible = msoTrue
.Fill.Transparency = 1#
.Line.Visible = msoFalse
End With

[Bob Phillips]

and the rest



Dim Diam As Single
Dim myDocument As Worksheet
Dim shp As Shape

Set myDocument = Worksheets(1)
Diam = Range("E2").Value 'pulls in volume from E2 in worksheet

Set shp = myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 150, 175)
With shp

.Fill.Visible = msoTrue
.Fill.Transparency = 1#
.Line.Visible = msoFalse
With .TextFrame
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
With .Characters
.Text = Diam
.Font.FontStyle = "Bold"
.Font.ColorIndex = 2
End With
End With
End With

[dfinkels]

SOLVED. Thanks El Xid, distinguished Lord of VBAX !!!!