在 VBA Excel 中弹出图表

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/18888473/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 23:24:30  来源:igfitidea点击:

Pop up charts in VBA Excel

excelexcel-vbavba

提问by gussilago

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.

我想知道是否有一种方法可以根据在特定工作表中找到的值,通过按下按钮在 Excel 中创建弹出图表?
最好的方法是能够在 VBA 中做到这一点。

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

我一直在研究,但找不到任何真正的解决方案。

Any suggestions?

有什么建议?

回答by Siddharth Rout

You! You lucky guy! :p

你!你这个幸运的家伙!:p

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

由于我是免费的,所以我为您创建了一个基本版本。:)

Requirement: Show Chart in a Userform

要求:在用户表单中显示图表

Logic:

逻辑

  1. Create a Userform and place an image control and a command button in it.
  2. Identify your chart's data range
  3. Add a Temp sheet
  4. Create your chart in the temp sheet
  5. Export the chart as a bmp to the user's temp directory
  6. Load the image control with that image
  1. 创建一个用户窗体并在其中放置一个图像控件和一个命令按钮。
  2. 确定图表的数据范围
  3. 添加临时表
  4. 在临时表中创建图表
  5. 将图表作为 bmp 导出到用户的临时目录
  6. 使用该图像加载图像控件

Assumptions:

假设

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

我假设您图表的数据范围在 [Sheet1] 中,如下所示。请相应地修改代码。

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

在此处输入图片说明