Bold font in shapes

[Brettjg]

I've been trying to work around this but don't seem to be getting anywhere. I
would much prefer not to have to select the shape array in order to make the
font bold, but the only way seems to be to do them individually with
..textframe (and because there might be 15 shapes in the array........forget
it)

This works for BOTH shapes:
ActiveSheet.Shapes.Range(Array("Text Box 26900", "Text Box 26901")).Select
With Selection
.Font.FontStyle = "Bold"
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.ForeColor.SchemeColor = 52
End With

and this works for ONLY ONE shape (because of .textframe)
With ActiveSheet.Shapes("Text Box 26900")
.TextFrame.Characters.Font.Bold = True
.Fill.Visible = msoTrue
.Fill.ForeColor.SchemeColor = 52
End With

Does anyone have a solution, or am I stuffed? Regards, Brett.

 

[Rick Rothstein]

Give this code a try (expand the array as far as needed)...

Dim S As Shape
For Each S In ActiveSheet.Shapes.Range(Array( _
"Text Box 26900", "Text Box 26901"))
S.TextFrame.Characters.Font.Bold = True
S.Fill.Visible = msoTrue
S.Fill.ForeColor.SchemeColor = 52
Next

 

[Rick Rothstein]

While the following is probably not what you want, I thought I would mention
it just in case. If you want to apply your code to **EVERY** shape on the
active sheet, you do not need to use an array at all...

Dim S As Shape
For Each S In ActiveSheet.Shapes
S.TextFrame.Characters.Font.Bold = True
S.Fill.Visible = msoTrue
S.Fill.ForeColor.SchemeColor = 52
Next

I emphasized *every* shape because many items that can be placed on a sheet
are, in fact, shapes... for instance, a Comment is a shape, so the above
code would change those as well.

 

[Brettjg]

Thanks very much Rick, I think that will do it. I always forget about using
the very useful "For". Regards, Brett

 

[Walter Briscoe]

In message <[email protected]> of Thu, 5 Mar 2009
08:06:43 in microsoft.public.excel.programming, Leith Ross

Brettjg;256205 Wrote:
[snip]

This works for BOTH shapes:
ActiveSheet.Shapes.Range(Array("Text Box 26900", "Text Box
26901")).Select
With Selection
.Font.FontStyle = "Bold"
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.ForeColor.SchemeColor = 52
End With

and this works for ONLY ONE shape (because of .textframe)
With ActiveSheet.Shapes("Text Box 26900")
.TextFrame.Characters.Font.Bold = True
.Fill.Visible = msoTrue
.Fill.ForeColor.SchemeColor = 52
End With

Does anyone have a solution, or am I stuffed? Regards, Brett.

Hello brettjg,

You can loop through the array and change the shape's property without
selecting it.

EXAMPLE:
--------------------------------------------------------
Dim Shp As Variant

For Each Shp In Array("Text Box 26900", "Text Box 26903")
With ActiveSheet.Shapes(Shp)
TextFrame.Characters.Font.Bold = True
Fill.Visible = msoTrue
Fill.ForeColor.SchemeColor = 52
End With
Next Shp

I found this question REALLY useful as previously I had only used
recorded macros for this situation.
I am supplied with a file containing a sheet which has a group of
rectangles.

I extract things tried from this thread with comments.
ActiveSheet.Shapes.Range(Array("Rectangle 12", "Rectangle 20", "Rectangle 22")).Select
Selection.Font.FontStyle = "Bold" ' This has no problem.
Selection.Characters.Text = "xx" ' This does nothing

' "Rectangle 6" is in "Group 1". If not ungrouped, writing to Selection.Characters.Text
' gets Run time error 1004, Unable to set the Text property of the Characters class
' If already ungrouped, ActiveSheet.Shapes("Group 1").Select gets
' "The item with the specified name wasn't found"
ActiveSheet.Shapes("Group 1").Select
Selection.ShapeRange.Ungroup.Select

' Both of these ideas are copied from this thread.
Dim Shp As Variant

For Each Shp In Array("Rectangle 12", "Rectangle 20", "Rectangle 22")
With ActiveSheet.Shapes(Shp)
.Characters.Text = "" ' Gets 438 object doesn't support this property or method
End With
Next Shp

Dim S As Shape
For Each S In ActiveSheet.Shapes.Range(Array("Rectangle 12", "Rectangle 20", "Rectangle 22"))
S.Characters.Text = "" ' Gets 438 object doesn't support this property or method
Next

' This works, in the absence of a group, but I dislike repeating it.
ActiveSheet.Shapes("Rectangle 12").Select
Selection.Characters.Text = ""
ActiveSheet.Shapes("Rectangle 20").Select
Selection.Characters.Text = ""
ActiveSheet.Shapes("Rectangle 22").Select
Selection.Characters.Text = ""

I find all this stuff very unpredictable! ;(