Dealing with Shapes in Excel in a VBA sub routine

[mudvillejon]

I am in the process of teaching myself Visual Basic and programing in
Excel.

I have been modifying a program that pulls data from a spreadsheet and
creates a set of bubble charts. The original program was dealing with
4 categories of data. I had added an additional two.

This worked out fine for creating the categories and inputing data into
a spreadsheet via a form. But in the subroutine to create the chart I
hit a bug. I copied and modified some lines of code that I don't
entirely understand, and not surprizingly the subroutine will not run.


The portion of the code that has me stumped is below. The jj in the
comments is me just so I can keep track of the comments I was putting
in while I figured out the program

The code difines objects - shapes - rectangles with a number (11-14).
Then it applies methods to to them. I had added. I added 15 and 16.
The editor did not recognize these objects. Probably because they
either don't exist in the chart or they are not defined anywhere. My
problem is that I have looked everywhere trying to find these shapes
and have pretty much hit a wall. The documentation has not been
helpful.

Can anyone point me in the right direction? Where should I be looking
for these rectangle shapes, and how can I define/create the additional
two I need to chart the two other data categories I have created?

Thanks in advance.

Jon


--------------------------The
code----------------------------------------

'To show the legend if the type of report is all
If strType = "All" Then
'this is for a full report "All"
Charts(conChartName).Shapes("Group 16").Visible = True 'not
sure what Group 16 is JJ

'jj makes shapes visible
Charts(conChartName).Shapes("Rectangle 11").Visible = True
Charts(conChartName).Shapes("Rectangle 12").Visible = True
Charts(conChartName).Shapes("Rectangle 13").Visible = True
Charts(conChartName).Shapes("Rectangle 14").Visible = True
'Charts(conChartName).Shapes("Rectangle 15").Visible = True 'JJ
added for SVC
'Charts(conChartName).Shapes("Rectangle 16").Visible = True
'JJ added for SVC

'jj these set a group of shapes on the chart and associate
them with the security imperatives defined in cells BF1-6 on the
projects sheet
'which are set from frmInvSetup It provides the caption on
the chart key
Charts(conChartName).Shapes("Rectangle 11").DrawingObject.Text
= Sheets("Projects").Range("BF1").Cells
Charts(conChartName).Shapes("Rectangle 12").DrawingObject.Text
= Sheets("Projects").Range("BF2").Cells
Charts(conChartName).Shapes("Rectangle 13").DrawingObject.Text
= Sheets("Projects").Range("BF3").Cells
Charts(conChartName).Shapes("Rectangle 14").DrawingObject.Text
= Sheets("Projects").Range("BF4").Cells
'Charts(conChartName).Shapes("Rectangle 15").DrawingObject.Text
= Sheets("Projects").Range("BF5").Cells 'JJ added for SecureValueChain
'Charts(conChartName).Shapes("Rectangle 16").DrawingObject.Text
= Sheets("Projects").Range("BF6").Cells 'JJ added for SVC
Else
Charts(conChartName).Shapes("Group 16").Visible = False
Charts(conChartName).Shapes("Rectangle 11").Visible = False
Charts(conChartName).Shapes("Rectangle 12").Visible = False
Charts(conChartName).Shapes("Rectangle 13").Visible = False
Charts(conChartName).Shapes("Rectangle 14").Visible = False
'Charts(conChartName).Shapes("Rectangle 15").Visible = False
'JJ added for SVC
'Charts(conChartName).Shapes("Rectangle 16").Visible = False
'JJ added for SVC

End If

 

[Peter T]

Charts(conChartName).Shapes("Group 16").Visible = True 'not

sure what Group 16 is JJ

Might not be anything. At one time referred to a group of shapes named
"Group 16". Put some shapes on a new sheet, select them all, right click and
click Group.

Best know what you've got on your chart sheet, eg

Dim shp As Shape
For Each shp In Charts("Chart1").Shapes
Debug.Print shp.Name
Next

press ctrl-g to see the Immediate Window

I added 15 and 16.
The editor did not recognize these objects. Probably because they
either don't exist in the chart or they are not defined anywhere.

If you gave them those names and didn't delete them they should exist.
Select them and look in the Name box left on Input bar (you can manually
rename them there too), or the above macro

Charts(conChartName).Shapes("Rectangle 11").DrawingObject.Text
= Sheets("Projects").Range("BF1").Cells

Delete the ".Cells"

Record a macro to get the basic syntax for adding rectangles, then remove
all the select('ion) stuff.

Instead of If..else to change the visible properties

dim bVis as boolean
' set bVis = True or false

..Shapes("Rectangle 11").Visible = bVis

I trust this legend is not for series names which is handled very nicely by
Excel. FWIW the text in your shapes could be 'linked' to those cells, ie
text would update when cell value(s) change.

Regards,
Peter T