Chart into Userform Problem

[David Adamson]

Can some kind sole please help me here.

I am display 13 charts one at a time into a userform via a drop down box
titled: ChartType

I have adapted the coding from John Walkenbach's site
http://j-walk.com/ss/excel/tips/tip66.htm

But the problem I get is that it displays the wrong chart. It works fine
for Chart 1 but after that it just seams to go loopy when I try and load in
another chart

Any suggestions would be greatly appreciated.



--------------------------------------------
Dim ChartNo As Integer

Private Sub GetChart_Click()
ChartNo = 0
CheckValue = Charts.ChartType.ListIndex + 1

ChartNo = CheckValue
UpdateChart

End Sub


Private Sub UpdateChart()

Set CurrentChart = Sheets("Charts").ChartObjects(ChartNo).Chart
CurrentChart.Parent.Width = 420
CurrentChart.Parent.Height = 190

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image1.Picture = LoadPicture(Fname)
End Sub

 

[Tushar Mehta]

Don't quite know what you mean by "it just seems to go loopy." Does it
mean that you get the wrong chart? No chart? Loops forever? Crashes?
Something else?

If the first, it could be because you are referencing charts (or more
accurately, chartobjects) by their numeric index. Unfortunately, there
is nothing concrete about how those numbers relate to the charts on a
worksheet. For example, if you exchange the locations of two charts,
it doesn't change their numeric index.

You might be better off giving each chartobject a name and using that
for index purposes.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[steve]

David,

Double check the chart title or number for each of the charts. If you hold
down the Ctrl key and click on the chart - the name will appear in the Name
Box (just to the left of the formula bar). You may find that what you
thought was Chart2 is instead Chart5 (or some such).
If this is the case, you'll have to rename each of the charts to get them
into the order you want.
You do that by going into the Name Box and typing in the name you want. But
be careful, Excels "spits" if you try to use a name already in use.

steve

 

[Geeves]

Thanks for the comments.

Excel was bringin up the wrong chart. Either jumping 3 to 5 ahead.

I doubled checked the Chart names and they were correct.

I will now try the suggestions by Tushar Mehta regarding "the chartobject a
name and using that
for index purposes."

I'll see how it goes

 

[Geeves]

I double checked all Chart Names, even checked what the macor would be
called for each.

I have gone through every thing I can think of and I have tracked down the
porblem to the set currentchart when I changed the UpdateChart Macro

I included a msgbox to see what it was actually referencing. When I ran it
I found that for Chart 2 it was selecting Chart 6
So despite ChartNo =2 it was giving me Chart 6 in the MSgBox anf then Chart
6 would be appearing in the form.

Can anyone tell me why it would be giving me the error as this is totally
beyond me.



Private Sub UpdateChart()
currentchart = ""
testChartName = ""
Set currentchart = Sheets("Charts").ChartObjects(ChartNo).Chart
' CName = currentchart

Dim testChartName As String
testChartName = Sheets("Charts").ChartObjects(ChartNo).Name


MsgBox testChartName


currentchart.Parent.Width = 420
currentchart.Parent.Height = 190

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
currentchart.Export Filename:=Fname, FilterName:="GIF"


' Show the chart
Image1.Picture = LoadPicture(Fname)
End Sub

 

[Tushar Mehta]

As I explained, or tried to explain, the number that XL uses to refer
to an object has nothing to do with the name of the object.

Suppose you created 6 charts (or chartobjects) in succession. XL, by
default, would name them Chart 1 through Chart 6. So, you could refer
to them as .ChartObjects("Chart 1") through .ChartObjects("Chart 6").
In addition, you could also refer to them as .ChartObjects(1) through
....(6).

Now, suppose you delete the first five objects created. There will be
only one chartobject left on the worksheet and you would refer to it as
..ChartObjects(1). However, XL doesn't change the name of the object.
So, it would still be Chart 6.

You can always change the name of the chartobject with .ChartObjects
("Chart 6").Name="MyChartObject". After this change, the reference to
..ChartObjects("Chart 6") would be invalid and you would have to refer
to .ChartObjects("MyChartObject").

An easy 'trick' to change the name immediately after creating a new
chart is mySheet.ChartObjects(mySheet.ChartObjects.Count).Name="myName"

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Are you declaring variables? You have two lines in conflict:

currentchart = ""

and

Set currentchart = Sheets("Charts").ChartObjects(ChartNo).Chart

You should have a line that says

Dim currentchart As Chart

That lecture on best programming practices aside (as if I'm the expert),
you need to refer to the chart names, not their numbers:

testChartName = "My Chart"
Set currentchart = Sheets("Charts").ChartObjects(testChartName ).Chart

- Jon

 

[Geeves]

Jon,

Thanks for the tip.

I ended up using the msgbox to work out what graph was being called.

Then I just changed the order they were in the drop down box and everything
is now fine.