Steve. S

How do I display a modified chart when a command button (cmdShowTemp) is clicked on a worksheet I used the code generated from a macro I recorded creating a chart using the wizard - using the data and making the changes I would like to see i.e. wider chart area, fill color etc, for this command. But I notice in the code the line Charts.Add, which I assume is why every time this command is clicked the Chart number increases and the references in the code to make the changes i.e. ActiveSheet.Shapes("Chart 6").ScaleWidth 1.39,..... I want are for the previous chart and an error message is produced (The item with the specified name wasn't found). Basically what Im trying to achieve is the same modified chart to show each time and to be updated whenever the data is changed or added. Im a novice at VBA so Im assuming Im not doing something very simple but Ive been trying different things for a couple days now and Im not getting anywhere, so I would appreciate any help. Can I also ask what code would I use to make the chart disappear of the screen so the user can view the data in the worksheet.

Re: How do I display the same modified embedded chart each time?


Moved from Visual Basic General to VBA forums.

Re: How do I display the same modified embedded chart each time?

Andy Pope


If you only want the chart to adjust when adding or removing data from cells then have a read of Jon's page on dynamic charts.

If you want to format elements of the chart when pressing a button then you need some way of referencing the chart and you certainly need to remove the code line that adds a new chart each time.
If you only have 1 chart on the worksheet you can use something like this

With ActiveSheet.ChartObjects(1).Chart
' make plot area RED
.PlotArea.Interior.ColorIndex = 3
End With

If you have multiple chartobjects you can use it's name. To see, and change if required, the chart objects name select the chartobject whilst holding the Shift key. This will select the object rather than the chart. The sizing handles will be white circles rather than black squares.
The chartobjects name will now appear in the Name Box, next to the formula bar.
Edit this to change the name.

Once you have the name you can then use

With ActiveSheet.ChartObjects("Chart 1").Chart
' make plot area GREEN
.PlotArea.Interior.ColorIndex = 4
End With

Re: How do I display the same modified embedded chart each time?

Steve. S

Thank you - every little suggestion is helpful.