Insert charts in userform

jdcar

Board Regular
Joined
Apr 27, 2012
Messages
163
Hi everyone
I try as much as possible to be self-taught, and I face a difficulty that I cannot understand. The question is as follows:
In my spreadsheet I have a project which data, I prepared two different graphs. And the goal is to insert that charts in userform.
I introduced the respective codes, it worked and I recorded all the project. I left this project and entered to another consultation. Moments later, I opened the project above again, when, it is not my amazement, it gives an error. (Run time error "481": invalid picture).
I do not know what to do. Is there anyone who can help me? I would be very grateful for the help. thank you
take care with covid19
Jdcar
VBA Code:
Dim currentchart As Chart
Dim ChartNum As Integer
''
Private Sub UserForm4_Initialize()
    ChartNum = 2
    UpdateChart 1

End Sub

Private Sub CboMonth_Change()

Sheets("ResumoDados").Range("c76").Value = CboMonth.Value
UpdateChart
UpdateChart1
End Sub

Private Sub cboGrDesp_Change()
Sheets("ResumoDados").Range("b76").Value = cboGrDesp.Value

UpdateChart
UpdateChart1

End Sub
''
Private Sub cmdout_Click()
    Unload Me
End Sub
Private Sub UpdateChart()

    Set currentchart = Sheets("charts").ChartObjects(1).Chart
    
       currentchart.Parent.Width = 580
       currentchart.Parent.Height = 220
'
'   Save chart as GIF
     PicFilename = ThisWorkbook.Path & "\" & "Mychart.gif"
    currentchart.Export Filename:=PicFilename, filtername:="GIF"

'   Show the chart
    Image1.Picture = LoadPicture(PicFilename)  ''<==Here is where Debug appear

End Sub

Private Sub UpdateChart1()

    Set currentchart = Sheets("charts").ChartObjects(2).Chart

    currentchart.Parent.Width = 580
    currentchart.Parent.Height = 220

'   Save chart as GIF
     PicFilename = ThisWorkbook.Path & "\" & "Mychart.gif"
    currentchart.Export Filename:=PicFilename, filtername:="GIF"

'   Show the chart
    Image2.Picture = LoadPicture(PicFilename)
    
End Sub
    
Private Sub cmdAll_Click()
UserForm6.Show

End Sub

Private Sub UserForm_Initialize()
   ChartNum = 2
  CboMonth.List = Array("JANEIRO", "FEVEREIRO", "MARÇO", "ABRIL", "MAIO", "JUNHO", "JULHO", "AGOSTO", _
                  "SETEMBRO", "OUTUBRO", "NOVEMBRO", "DEZEMBRO")
  cboGrDesp.List = Array("Saúde", "Transporte", "Alimentação", "Desp.Fixas", "Impostos", "Desp.Ocasion", "AAA", "BBB", _
                  "CCC")

End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
Hi jdcar. Trial adding the name of the userform before the image control....
Code:
Userform1.Image1.Picture = LoadPicture(PicFilename)
HTH. Dave
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
Sorry I can't help jdcar. The rest of your code looks OK. You could trial explicitly declaring all of your variables (Ie. Option Explicit at the top of your code and then Dim statements). Sometimes XL gets mixed up but I don't see how that helps here. You could also trial using 2 different names for your picture files ie mychart1.gif and mychart2.gif. I suspect that probably won't help either however. Maybe others will have some suggestions. Stay safe. Dave
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,962
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Just checking: Does the picture file exist? If so, can you open it in an image editor?

I assume your code is in the code module of the user form with the picture control. I just whipped up a quick test, and this works for me. I call it from a button click on the form, same difference. I even closed the file and reopened it, and it works fine.

VBA Code:
Private Sub UpdateChart()
  Dim cht As Chart
  Set cht = ThisWorkbook.Worksheets("Charts").ChartObjects(1).Chart
  With cht.Parent
    .Height = 150
    .Width = 225
  End With
  
  Dim ChartFileName As String
  ChartFileName = ThisWorkbook.Path & Application.PathSeparator & "chart.gif"
  cht.Export ChartFileName, "GIF"
  
  Me.Image1.Picture = LoadPicture(ChartFileName)
End Sub
 

jdcar

Board Regular
Joined
Apr 27, 2012
Messages
163
Hi Dave
I really appreciated your feedback, and I thank you so much for the effort.
I made several attempts, making some changes, and it worked, but if it closed and reopened with other name, it would no longer work.
There is an issue that I did not mention, but I think it should not be for that reason. The form has a command button for another form, and in this I have another image with a spinbutton to show various graphics. I used the code below:
VBA Code:
Dim ChartNum As Integer

Private Sub UserForm6_Initialize()
    ChartNum = 4
    UpdateChartB
End Sub

Private Sub spinB1_Change()
Call UpdateChart
End Sub

Private Sub UpdateChartB()
Sheets("resumodados").Range("t69").Value = spinB1.Value
Dim cht As Chart
  Set cht = ThisWorkbook.Worksheets("Charts").ChartObjects("chart 3").Chart
    With cht.Parent
      .Width = 570
      .Height = 216
    End With 
Dim ChartFileName As String
  ChartFileName = ThisWorkbook.Path & Application.PathSeparator & "mychart.gif"
  cht.Export ChartFileName, "GIF"
  Image1.Picture = LoadPicture(ChartFileName)  
End Sub

Private Sub cmdReturn_Click()
Unload Me
Load UserForm4
End Sub

Private Sub UserForm_Initialize()
spinB1.Value = 4
ChartNum = 4
End Sub

Hi Jon
I used the diferent way to show the code, and It works. But in the same way when I closed it and copy the project, and give another name, it lets to work.
And there is another very strange situation for me, which is:
after giving the error, I go to the spreadsheet and select, and then diselect, graph1 and graph2 and let it be in the respective spreadsheet ("Charts"). Then I open the userform and
call the graphics again, and now, they are operational again.

Thank you very much to all of you
Jdcar
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,962
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I don't know why closing, renaming, and reopening your workbook should lead to errors.

A couple general suggestions. They may seem OCD, but they will improve your code, reduce errors, and make it easier to read and maintain.

Put Option Explicit at the top of all of your modules. Also, go to Tools > Options and check Require Variable Declaration (and also uncheck Auto Syntax Check).

UserForm6_Initialize looks like the name of an event procedure that runs when UserForm6 is initialized, but it isn't. You should avoid procedure names that look like something they're not.

Reference objects carefully and completely. For example, refer to Sheets("resumodados") like this ThisWorkbook.Worksheets("resumodados"). In the same way, refer to controls on the UserForm like this:

VBA Code:
  Me.Image1.Picture = LoadPicture(ChartFileName)
  Me.spinB1.Value = 4
 

jdcar

Board Regular
Joined
Apr 27, 2012
Messages
163
Hi Dave and Jon
I think it might not be the right option, but I managed to overtake it with a macro that previously selected the graphics on the spreadsheet and introduced it in the UserForm_Initialize
Atter this solution I'll go to change the procedures as described above.
If I have any problem can I ask to you?
Anyway, my thanks to you and to Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,574
Members
416,925
Latest member
malamutus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top