Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 279
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,124
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 279
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 279
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,124
Default Record a macro will not edit composite drawing object

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Walter Briscoe" wrote in message
...
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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i prepare a macro to insert drawing object on specific cell Harshad[_2_] Excel Discussion (Misc queries) 2 November 25th 08 09:06 AM
Position drawing object relative to cell Horatio J. Bilge, Jr. Excel Discussion (Misc queries) 2 September 29th 08 09:16 PM
Saving from Excel to MS Office Drawing Object sir23 Excel Discussion (Misc queries) 4 February 19th 08 03:53 AM
how do i copy and inserted drawing object in a cell to multiple ce BROCK8292 Excel Discussion (Misc queries) 1 February 20th 07 10:11 PM
Automatically moving a drawing object in a chart Dan k Charts and Charting in Excel 1 February 7th 06 04:06 AM


All times are GMT +1. The time now is 09:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"