Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Record a macro will not edit composite drawing object
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. -- Walter Briscoe |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Record a macro will not edit composite drawing object
ActiveSheet.Shapes("shapename").TextFrame.Characte rs.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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Walter Briscoe" wrote in message ... 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. -- Walter Briscoe |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Record a macro will not edit composite drawing object
In message of Wed, 7 Jan 2009
18:30:44 in microsoft.public.excel.newusers, Don Guillett writes 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.Charact ers.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 -- Walter Briscoe |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Record a macro will not edit composite drawing object
In message of Thu, 8 Jan 2009
17:29:05 in microsoft.public.excel.newusers, Walter Briscoe writes In message of Wed, 7 Jan 2009 18:30:44 in microsoft.public.excel.newusers, Don Guillett writes Thank you! How do I tell which shapes exist and their names? 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.) -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i prepare a macro to insert drawing object on specific cell | Excel Discussion (Misc queries) | |||
Position drawing object relative to cell | Excel Discussion (Misc queries) | |||
Saving from Excel to MS Office Drawing Object | Excel Discussion (Misc queries) | |||
how do i copy and inserted drawing object in a cell to multiple ce | Excel Discussion (Misc queries) | |||
Automatically moving a drawing object in a chart | Charts and Charting in Excel |