Record a macro will not edit composite drawing object

  • Thread starter Thread starter Walter Briscoe
  • Start date Start date
W

Walter Briscoe

I use Excel 2003 (11.5612.5606).

I am supplied with a file which I want to edit with macros.

It contains a rectangular object, consisting of abutting rectangles.
When I click a rectangle, it is outlined and I can change text in it.
In "Record a macro", I click a rectangle & a Format Object dialog opens.
Why the difference?
How can I select a rectangle to change text in it in a macro.
 
ActiveSheet.Shapes("shapename").TextFrame.Characters.Text = "whatever"

I use this to do a bunch at once.
Sub NameShapes()
Sheets("checks").Select
For Each c In [setup!a4:a15]
ActiveSheet.Shapes(c).TextFrame. _
Characters.Text = c.Offset(0, 1)
Next c
End Sub
 
In message <[email protected]> of Wed, 7 Jan 2009
18:30:44 in microsoft.public.excel.newusers, Don Guillett

Thank you! How do I tell which shapes exist and their names?

I used this code:
For Each s In ActiveSheet.Shapes
MsgBox s.Name
Next s
and learnt that I have "Group 1", "Picture 27" and "Rectangle 28" to
"Rectangle 37" inclusive.
Looping on MsgBox s.TextFrame.Characters.Text (unsurprisingly) gave me a
type mismatch diagnostic.

I REALLY don't know the VBA debugger. How to I display what is in
ActiveSheet.Shapes("Rectangle 28") from the debugger?

A series of operations like ActiveSheet.Shapes("Rectangle
28").TextFrame.Characters.Text = "28" allowed me to identify those
rectangles and confirmed to me I need to analyse "Group 1". How?

I can have a macro write to any of "Rectangle *".

Right-clicking a rectangle gives me a Grouping control which would allow
me to change the contents of a group, but I see nothing to display a
group.
ActiveSheet.Shapes("shapename").TextFrame.Characters.Text = "whatever"

I use this to do a bunch at once.
Sub NameShapes()
Sheets("checks").Select
For Each c In [setup!a4:a15]
ActiveSheet.Shapes(c).TextFrame. _
Characters.Text = c.Offset(0, 1)
Next c
End Sub
Click to expand...
 
In message <[email protected]> of Thu, 8 Jan 2009
17:29:05 in microsoft.public.excel.newusers, Walter Briscoe
In message <[email protected]> of Wed, 7 Jan 2009
18:30:44 in microsoft.public.excel.newusers, Don Guillett

Thank you! How do I tell which shapes exist and their names?
Click to expand...

I got no reply, but soldiered on. I now have something like:
' Grab "Group 1" and split it into components
ActiveSheet.Shapes(1).Select
Selection.ShapeRange.Ungroup.Select
' Write 12345 in Arial, Bold, right-justified into Rectangle 6
ActiveSheet.Shapes("Rectangle 6").Select
Selection.Characters.Text = "12345"
With Selection.Characters(Start:=1, Length:=5).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.HorizontalAlignment = xlRight
' Form a new group
ActiveSheet.Shapes.Range(Array("Rectangle 2", "Rectangle 3" _
, "Rectangle 4", "Rectangle 5" _
, "Rectangle 6", "Rectangle 7", "Rectangle 8", "Rectangle 9" _
, "Rectangle 10", "Rectangle 11", "Rectangle 12", "Rectangle 13" _
, "Rectangle 14", "Rectangle 15", "Rectangle 16", "Rectangle 17" _
, "Rectangle 18", "Rectangle 19", "Rectangle 20", "Rectangle 21" _
, "Rectangle 22", "Rectangle 23", "Rectangle 24", "Rectangle 25" _
, "Text Box 26")).Select
Selection.ShapeRange.Regroup.Select

This does what I want but replaces "Group 1" with "Group 38".
It also requires me to know how "Group 1" is composed.
There must be a better way!

(I have enabled the Draw Toolbar and ensured the Select Multiple Objects
button is enabled in it.)
 
Does this help?
Sub listem()
On Error Resume Next
i = 1
For Each sh In Sheets("CHECKS").Shapes
'MsgBox sh.Name
Cells(i, 1) = sh.Name
i = i + 1
Next
End Sub
 
Back
Top