Display image in userform

[Newtonpersquaremetre]

[h=1][/h]

<tbody style="margin: 0px; padding: 0px; border: 0px;">
[TD="class: votecell"]

up vodown votefavorite
​

[/TD]
[TD="class: postcell"]Hello!

I have some data on a table("FSTHR_tests") in the worksheet ("Project Results") and want to extract some of the data to make some graphs, which will be diplayed in UserForm1. Below is my attempt to the code. I get the error: 'Method 'Values" of object "Series" failed. Any assistance would be greatly appreciated!
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Private Sub cmdLoad_Click()
If ComboBox1.Text = "Select Chart" Then


MsgBox "Select a chart from the dropdown list"
Exit Sub


Dim MyChart As Chart
Dim ChartData As Range
Dim chartIndex As Integer
Dim ChartName As String


chartIndex = ComboBox4.ListIndex


Select Case chartIndex


Case 0
Set ChartData = Worksheets("Project Results").ListObjects("FSTHR_tests").ListColumns("Avg-Burn Time (s)").DataBodyRange
ChartName = Avg_Burn_Time
End Select
End If
Application.ScreenUpdating = False


Set MyChart = Worksheets("Project Results").Shapes.AddChart(xlXYScatterLines).Chart
MyChart.SeriesCollection.NewSeries
MyChart.SeriesCollection(1).Name = ChartName
MyChart.SeriesCollection(1).values = ChartData
MyChart.SeriesCollection(1).XValues = Worksheets("Project Results").ListObjects("FSTHR_tests").ListColumns("#")


Dim imageName As String
imageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.gif"
MsgBox "The default file path is " & Application.DefaultFilePath
MyChart.Export Filename:=imageName, FilterName:="GIF"


Worksheets("Project Results").ChartObjects(1).Delete


Application.ScreenUpdating = True


UserForm1.Image1.Picture = LoadPicture(imageName)
</code>Please note that the case is not done yet. There will be many more graphs to follow. I only want to get my code working for one graph. Further, The error is on the line <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">MyChart.SeriesCollection(1).values = ChartData</code>


[/TD]

</tbody>

 

[RickXL]

Hi,

I think your End If is in the wrong place so that ChartData is never set.

Also I think you might need to change the XValues line:

    MyChart.SeriesCollection(1).XValues = Worksheets("Project Results").ListObjects("FSTHR_tests").ListColumns("#").DataBodyRange

Regards,

 

[Newtonpersquaremetre]

Yes that was the problem! Thanks for the help.

 

[Newtonpersquaremetre]

If anyone is interested in displaying X and Y values from a table in a Userform

Here is my 'final code'. I thought I might just post it here, in case that someone is having the same roblem. I now have two comboboxes on my userform to select x and y values from my table.

Private Sub cmdLoad_Click()


Dim MyChart As Chart
Dim ref As ListObject
Dim refx As ListObject
Dim chartY As Integer
Dim chartX As Integer
Dim ChartName As String
Dim chartDataY As Range
Dim chartDataX As Range


chartY = ComboBoxY.ListIndex
If ComboBoxY.Text = "Y_Values" Then
MsgBox "Select Y Values"
Exit Sub
End If


chartX = ComboBoxX.ListIndex
If ComboBoxX.Text = "X_Values" Then
MsgBox "Select X Values"
Exit Sub
End If


Select Case chartY
Case 0
Set ref = Worksheets("Project Results").ListObjects("FSTHR_tests")
Set chartData = ref.ListColumns("Avg-Burn Time (s)").DataBodyRange
ChartName = Avg_Burn_Time
End Select




Select Case chartX
Case 0
Set refx = Worksheets("Project Results").ListObjects("FSTHR_tests")
Set chartDataX = refx.ListColumns("#").DataBodyRange
End Select


Application.ScreenUpdating = False


Set MyChart = Worksheets("Project Results").Shapes.AddChart(xlXYScatterLines).Chart
MyChart.SeriesCollection.NewSeries
MyChart.SeriesCollection(1).Name = ChartName
MyChart.SeriesCollection(1).values = chartData
MyChart.SeriesCollection(1).XValues = chartDataX


Dim imageName As String
imageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.gif"
MsgBox "The default file path is " & Application.DefaultFilePath
MyChart.Export Filename:=imageName, FilterName:="GIF"


Worksheets("Project Results").ChartObjects(1).Delete


Application.ScreenUpdating = True


UserForm1.Image1.Picture = LoadPicture(imageName)


End Sub

 

[Newtonpersquaremetre]

Re: If anyone is interested in displaying X and Y values from a table in a Userform

The only thng now is, That the chart is too small. It does not fill the entire image space that I have created on my userform.

 

[RickXL]

Re: If anyone is interested in displaying X and Y values from a table in a Userform

I suspect you need to change a Property of the Image Object.

The default value I had for PictureSizeMode was "0 - frm PictureSizeModeClip". If that is the same for you, click on the dropdown for that field and choose: "1 - frm PictureSizeModeStretch", instead.

Regards,

 

[Newtonpersquaremetre]

Re: If anyone is interested in displaying X and Y values from a table in a Userform

Yes I have tried that. But the picture quality decreases drastically if you do that. I then figured out that you can change the actual size of your graph with the following code:

With MyChart.Parent         .Height = 400
         .Width = 775
     End With