Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dealing with Shapes in Excel in a VBA sub routine
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dealing with Shapes in Excel in a VBA sub routine
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 wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel document with shapes on it but the shapes do not print | Excel Worksheet Functions | |||
Naming Auto Shapes and Creating new Shapes | Excel Discussion (Misc queries) | |||
When drawing shapes in excel the shapes keep disappearing | Excel Discussion (Misc queries) | |||
How can i get more 3D shapes for Auto shapes in excel? | Excel Discussion (Misc queries) | |||
How can i get more 3D shapes for Auto shapes in excel? | Excel Discussion (Misc queries) |