在 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
Pop up charts in VBA Excel
提问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:
逻辑:
- 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
- 创建一个用户窗体并在其中放置一个图像控件和一个命令按钮。
- 确定图表的数据范围
- 添加临时表
- 在临时表中创建图表
- 将图表作为 bmp 导出到用户的临时目录
- 使用该图像加载图像控件
Assumptions:
假设:
I am assuming that your chart's data range is in [Sheet1] and look like this. Please amend the code accordingly.
我假设您图表的数据范围在 [Sheet1] 中,如下所示。请相应地修改代码。
Preparing your Userform
准备你的用户表单
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.
当您运行用户表单并按下命令按钮时,您将看到图像填充在用户表单中。