Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Shapes & Charts

  Asked By: Lloyd    Date: Sep 08    Category: MS Office    Views: 603
  

I (a relative newcomer to VBA) am writing an application to automate the
processing and graphical presentation of data. There are six graphs on three
chart sheets and a number of the charts have a couple of schematic diagrams
included to clarify points to the user/client. Below is a section of the code,
which was mostly obtained from the macro recorder. Basically, I have three
schematic diagrams stored on worksheets(1). These each have a specific name
(determined by Excel) - Group 67, Group 68 and Group 69. Dependent upon the
magnitude of an input variable (gs) the appropriate schematic is selected,
copied
and pasted onto one of the charts and then moved into position. This works
fine - first time round. The intention was to permit the user to run the
routine
any number of times by simply selecting the pasted schematic, deleting it
and then running the copy/paste procedure as described again. Unfortunately,
the schematic (which consists of a number of shapes and lines grouped together)
does not retain the same name after being pasted into the chart, and appears
to be assigned a different name every time it is pasted. So my question - how
can I label or name or identify the schematic so that I can delete it? Or
does anyone have any other suggestions as to how to approach this problem?

gs = Val(Worksheets(1).Cells(4, 19))

If gs = 0 Then gp = 67
If gs > 0 Then gp = 68
If gs < 0 Then gp = 69

gpname = "Group " & gp

Worksheets(1).Activate
ActiveSheet.Shapes(gpname).Select
Selection.Copy
Sheets("ChartSheet").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.Paste
Selection.ShapeRange.IncrementLeft 467.24
Selection.ShapeRange.IncrementTop 246#

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Grant Jones     Answered On: Sep 08

You can assign a unique name to each schematic using the Name Box. The Name Box
is the textbox on the left side of the formula bar. It displays the address of
the active cell. When you select one of your schematic groups, the name of that
group is displayed in the Name Box. Click in the Name Box and type in a new
name. You can name ranges of cells the same way.

When you copy & paste the schematic to the chart  sheet, it will retain  the
name you assigned, and you can refer to it using that name. Here are some
unsophisticated examples of code:

Sub Macro1()
'Copies shape TestGroup1 from Sheet1 to Chart1
Sheets("Sheet1").Shapes("TestGroup1").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.Paste
End Sub

Sub Macro2()
'Activates the chart sheet (must do this!), selects
'the shape TestGroup1 and rotates it 90 degrees.
Sheets("Chart1").Activate
ActiveChart.Shapes("TestGroup1").Select
Selection.ShapeRange.IncrementRotation 90#
End Sub

Sub Macro3()
'Deletes TestGroup1 shape from the chart sheet
ActiveChart.Shapes("TestGroup1").Select
Selection.Delete
End Sub

 
Answer #2    Answered By: Phailin Jainukul     Answered On: Sep 08

Thanks very much for taking the time  to read and respond to my problem.
Your're answer solved it!!

 
Didn't find what you were looking for? Find more on Shapes & Charts Or get search suggestion and latest updates.




Tagged: