Pop up charts in VBA Excel

Question

I was wondering if there is a way to create pop-up charts in Excel with press of a button, based on values found in a specific worksheet?
The best way would be to be able to do it in VBA.

I have been researching but can't find any real solutions.

Any suggestions?

What do u mean pop up charts? Where do u want them to pop up> Userform? In the spreadsheet? As a new chart? — Siddharth Rout, Sep 19 '13 at 07:21
Currently this is unclear, I have not down voted (but others probably will) if some code (an attempt at code is expected in the question) and further specifics are not added to the question. — whytheq, Sep 19 '13 at 07:58
@SiddharthRout Sorry for late response! I would like to have a pop-up window, i.e. as a separate, temporary, window, showing the chart at hand. Don't know if it's possible, but that's why I am asking. — gussilago, Sep 19 '13 at 11:41
@whytheq It might be unclear, but how am i supposed to have code, if I don't even know if it is possible? Pseude-code? I definitely understand your arguments though... — gussilago, Sep 19 '13 at 11:41
Ok. I have answered a similar question somewhere and I need to search for it. In the meantime here is what you have to do. Via Code, `1` Create a temp sheet in which you will create the chart. `2` Export the Chart as an image to the user's temp directory `3` In the image control of the userform, set the path to that image `4` Delete the temp sheet. Give it a try and if you are stuck, post the code that you tried and we will take it from there. — Siddharth Rout, Sep 19 '13 at 11:58
Here is one link which exports charts as image.. http://stackoverflow.com/questions/11939087/export-chart-as-image-with-click-of-a-button — Siddharth Rout, Sep 19 '13 at 12:16
@SiddharthRout The code flows beautifully. However, when trying to export it to my "real" data, the UserForm1 object seems to be missing "Image1", resulting in error pasting the picture onto it. I have tried your code in a separate workbook, and then it works (having an Image1), but when trying the code in another workbook it fails. Encountered this issue before? — gussilago, Sep 20 '13 at 08:00
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/37718/discussion-between-siddharth-rout-and-gussilago) — Siddharth Rout, Sep 20 '13 at 08:03

score 11 · Accepted Answer · answered Sep 19 '13 at 12:40

You! You lucky guy! :p

Since I was free, I created a basic version for you. :)

Requirement: Show Chart in a Userform

Logic:

Create a Userform and place an image control and a command button in it.
Identify your chart's data range
Add a Temp sheet
Create your chart in the temp sheet
Export the chart as a bmp to the user's temp directory
Load the image control with that image

Assumptions:

I am assuming that your chart's data range is in [Sheet1] and look like this. Please amend the code accordingly.

enter image description here

Preparing your Userform

enter image description here

Code

This code goes in the userform code area. I have commented the code so that you will not have any problem understanding it. Still if you so, post back.

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim wsTemp As Worksheet
    Dim rng As Range
    Dim oChrt As ChartObject

    '~~> Set the sheet where you have the charts data
    Set ws = [Sheet1]

    '~~> This is your charts range
    Set rng = ws.Range("A1:B3")

    '~~> Delete the temp sheeet if it is there
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Sheets("TempOutput").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    '~~> Add a new temp sheet
    Set wsTemp = ThisWorkbook.Sheets.Add

    With wsTemp
        '~~> Give it a name so that we can delete it as shown above
        '~~> This is just a precaution in case `wsTemp.Delete` fails below
        .Name = "TempOutput"

        '~~~> Add the chart
        Set oChrt = .ChartObjects.Add _
            (Left:=50, Width:=300, Top:=75, Height:=225)

        '~~> Set the chart's source data and type
        '~~> Change as applicable
        With oChrt.Chart
            .SetSourceData Source:=rng
            .ChartType = xlXYScatterLines
        End With
    End With

    '~~> Export the chart as bmp to the temp drive
    oChrt.Chart.Export Filename:=TempPath & "TempChart.bmp", Filtername:="Bmp"

    '~~> Load the image to the image control
    Me.Image1.Picture = LoadPicture(TempPath & "TempChart.bmp")

    '~~> Delete the temp sheet
    Application.DisplayAlerts = False
    wsTemp.Delete
    Application.DisplayAlerts = True

    '~~> Kill the temp file
    On Error Resume Next
    Kill TempPath & "TempChart.bmp"
    On Error GoTo 0
End Sub

'~~> Function to get the user's temp path
Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
End Function

Output:

When you run the userform and press the command button, you will see the image populate in the userform.

enter image description here

@Siddharthrouth I don't know how to thank you enough! This just saved me a couple of hours! Exactly what I needed! — gussilago, Sep 19 '13 at 14:01

Pop up charts in VBA Excel

1 Answers1

Linked