Chart on a Userform

Hi All

I want to add a chart to a userform, and i have found a few codes and sites that offer help, but..... im having issues i just cant get it to work, currently i have this

Option Explicit
 Dim chartnum As Integer
 Private Sub UserForm_Initialize()
     chartnum = 1
     updatechart
 End Sub
 Private Sub updatechart()
     Dim CurrentChart As Chart
     Dim Fname As String
 
    
    Set CurrentChart = Sheets("Charts").ChartObjects(chartnum).Chart
     CurrentChart.Parent.Width = 390
     CurrentChart.Parent.Height = 190
     Fname = ThisWorkbook.Path & "\temp.gif"
     CurrentChart.Export Filename:=Fname, FilterName:="GIF"
     Image1.Picture = LoadPicture(Fname)
 End Sub

Which wont work, then i have looked at this site.... http://www.j-walk.com/ss/excel/tips/tip66.htm

And cant get that to work either, could somone please explain where i should be putting these codes? in a module, or directly into the userform code

Thanks

Dan

Yes i have the tab called "Charts" where the chart is located, and the image control in the userform is Image1, my current code for the whole userform is very simple

Private Sub CommandButton1_Click()
UserForm1.PrintForm
End Sub



Private Sub UserForm_Initialize()


TextBox1 = "Sales have " & IIf([n22>0], "in", "de") & "creased by " & Format([n22], "£#,###,##0") & ", Which is " & Format([O22], "##0.0%") & " " & IIf([n22>0], "higher than the MYR.", "lower than the MYR.")
TextBox2 = "Actual sold quantity has " & IIf([n13>0], "in", "de") & "creased by " & Format([n13], "#,###,##0") & ", Which is " & Format([O13], "##0.0%") & " " & IIf([n13>0], "higher than the MYR.", "lower than the MYR.")
TextBox3 = "Gross margin has " & IIf([n31>0], "in", "de") & "creased by " & Format([n31], "£#,###,##0") & ", Which is " & Format([O31], "##0.0%") & " " & IIf([n31>0], "higher than the MYR.", "lower than the MYR.")
TextBox4 = "The overall GM% is " & IIf([n40>0], "high", "low") & "er than the budget by " & Format([n40], "##0.0%.")
TextBox6 = "Sales have " & IIf([q22>0], "in", "de") & "creased by " & Format([q22], "£#,###,##0") & ", Which is " & Format([r22], "##0.0%") & " " & IIf([q22>0], "higher than last year.", "lower than last year.")
TextBox7 = "Actual sold quantity has " & IIf([q13>0], "in", "de") & "creased by " & Format([q13], "#,###,##0") & ", Which is " & Format([r13], "##0.0%") & " " & IIf([q13>0], "higher than last year.", "lower than last year.")
TextBox8 = "Gross margin has " & IIf([q31>0], "in", "de") & "creased by " & Format([q31], "£#,###,##0") & ", Which is " & Format([r31], "##0.0%") & " " & IIf([q31>0], "higher than last year.", "lower than last year.")
TextBox5 = "The overall GM% is " & IIf([q40>0], "high", "low") & "er than last year by " & Format([q40], "##0.0%.")

Me.TextBox9.Value = Range("D2").Value

TextBox1.Locked = True
TextBox2.Locked = True
TextBox3.Locked = True
TextBox4.Locked = True
TextBox5.Locked = True
TextBox6.Locked = True
TextBox7.Locked = True
TextBox8.Locked = True
TextBox9.Locked = True

End Sub

Its literally a summary box, but i want to extend this now to include graphs, when i put the previously posted code into the module i get this error....

1. when all the code is in the module = variable not defined on Image2
2. when i remove the userform_initialize sequence and put it in with my current code = Sub or function not defined, (which i then add "Run" infront of updatechart) and then it works, but doesnt show the graph nor does it save the GIF in the file location

When i change it to this....

Private Sub UserForm_Initialize()

Dim chartnum As Integer


TextBox1 = "Sales have " & IIf([n22>0], "in", "de") & "creased by " & Format([n22], "£#,###,##0") & ", Which is " & Format([O22], "##0.0%") & " " & IIf([n22>0], "higher than the MYR.", "lower than the MYR.")
TextBox2 = "Actual sold quantity has " & IIf([n13>0], "in", "de") & "creased by " & Format([n13], "#,###,##0") & ", Which is " & Format([O13], "##0.0%") & " " & IIf([n13>0], "higher than the MYR.", "lower than the MYR.")
TextBox3 = "Gross margin has " & IIf([n31>0], "in", "de") & "creased by " & Format([n31], "£#,###,##0") & ", Which is " & Format([O31], "##0.0%") & " " & IIf([n31>0], "higher than the MYR.", "lower than the MYR.")
TextBox4 = "The overall GM% is " & IIf([n40>0], "high", "low") & "er than the budget by " & Format([n40], "##0.0%.")
TextBox6 = "Sales have " & IIf([q22>0], "in", "de") & "creased by " & Format([q22], "£#,###,##0") & ", Which is " & Format([r22], "##0.0%") & " " & IIf([q22>0], "higher than last year.", "lower than last year.")
TextBox7 = "Actual sold quantity has " & IIf([q13>0], "in", "de") & "creased by " & Format([q13], "#,###,##0") & ", Which is " & Format([r13], "##0.0%") & " " & IIf([q13>0], "higher than last year.", "lower than last year.")
TextBox8 = "Gross margin has " & IIf([q31>0], "in", "de") & "creased by " & Format([q31], "£#,###,##0") & ", Which is " & Format([r31], "##0.0%") & " " & IIf([q31>0], "higher than last year.", "lower than last year.")
TextBox5 = "The overall GM% is " & IIf([q40>0], "high", "low") & "er than last year by " & Format([q40], "##0.0%.")

Me.TextBox9.Value = Range("D2").Value

TextBox1.Locked = True
TextBox2.Locked = True
TextBox3.Locked = True
TextBox4.Locked = True
TextBox5.Locked = True
TextBox6.Locked = True
TextBox7.Locked = True
TextBox8.Locked = True
TextBox9.Locked = True

chartnum = 1
Run updatechart

End Sub

And keep the updatechart code in the module, there are no errors, but it still doesnt give me the chart in the userform, or save the GIF

Hi Andy

Sorry i had to remove alot of sensitive data, and then overwrite the formulas to give some form of example of what i am after, here is the example

Thank you, hopefully you may be able to help me, yet again

Originally Posted by Andy Pope

I just incorporated the UpdateChart routine into your userform.

Hi Andy,
I know, it's a quite an old topic, I just found it. I tried to copy your code to mine, but I got runtime error.
Could you maybe check what the problem could be?

Tks

Brilliant thanks Andy Pope, i can only assume that i had another sheet open with userform1 in, as when i opened your returned sheet it wouldnt let me open the userform, when i closed all other sheets, it worked exactly how i wanted it too

Thanks again