Thread: Chart generated & closed with click of a button

Hi. I'm new to this forum and I come seeking help for a tasking I've been given at work. I want to make sure that I complete it in the most efficacious way possible. I use Excel 2007 at work.

There's an important monthly report that must be generated every month, and it contains various performance measures for our clinic. There's one column for each month in the year, and each row contains data for a specific metric across the months. The data would be more easily digestible if presented in line graph format.

My boss' boss wants me to add a column that contains a button for each row that generates a line chart when clicked. He doesn't want the button to contain a hyperlink to another worksheet that contains the chart; he wants the charts to come up popup box style and be closeable with a click of a button (versus deleting the chart).

How should I best do this? I know how to create a button and I'm pretty sure I can create a macro to generate the chart. I'm stuck on how to create a button to CLOSE the chart, though. Maybe there's a way to tell the macro to close the chart upon pressing the ESC key? Or better yet, have it close when you click the original chart button again?

Thank you very much for any insight you can provide.

Have a look at these approaches for selecting information to chart.
http://peltiertech.com/Excel/Charts/...#SheetControls

Note that a popup would require you to capture an image of the chart in order to display it.

Hi again. So, I've spent too many hours this weekend trying to make this work and I'm back for more help. I couldn't make dynamic line charting work even without incorporating it into a macro, so I'm skipping that whole idea altogether. This is what I have accomplished:

Code:

Sub Macro3()
'
' Macro3 Macro
'

'
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'APN'!$B$4:$M$6")
    ActiveChart.ChartType = xlLine
    ActiveChart.SeriesCollection(1).Name = "=""Number of Retakes (Tech)"""
    ActiveSheet.Shapes("Button 1").Select
    Selection.Characters.Text = "Close Chart"
    With Selection.Characters(Start:=1, Length:=11).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
      
    
End Sub

The above code creates the chart and renames the button, but now I can't figure out how to CLOSE the chart. I tried to name the chart as part of creating it so that I can then reference it specifically to delete, but I only got error messages when I tried that.

Any thoughts?

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I'll add the code tags for you this time, but I'll expect you to use them from now on.

What do you mean by close the chart?

Can you post example of your data layout and the chart you want creating

Hello again. When I say close the chart, I mean I'd like a way to delete the little chart that's created by clicking a button. I just want some functionality where a user could click a button, or click the red X like we do for windows, instead of having to right-click and then select cut. I'm attaching a redacted copy of the data file with the macro I started. I hope that makes it a little clearer what I'm going for.

Thank you for all your help.

This just uses the same button to do 2 tasks.

Code:

Sub Macro3()
'
' Macro3 Macro
'
    With ActiveSheet.Shapes(Application.Caller)
        If .TextFrame.Characters.Text = "Close Chart" Then
            ActiveSheet.ChartObjects(1).Delete
            .TextFrame.Characters.Text = "Create Chart"
        Else
            ActiveSheet.Shapes.AddChart.Select
            ActiveChart.SetSourceData Source:=Range("'DATA'!$B$4:$M$6")
            ActiveChart.ChartType = xlLine
            ActiveChart.SeriesCollection(1).Name = "=""Number of Retakes (Tech)"""
            .TextFrame.Characters.Text = "Close Chart"
        End If
    End With
    
End Sub

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save