Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart into Userform Problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart into Userform Problem
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 "David Adamson" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart into Userform Problem
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 "steve" wrote in message ... 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 "David Adamson" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart into Userform Problem
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart into Userform Problem
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).N ame="myName" -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart into Userform Problem
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 ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Geeves wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart into Userform Problem
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. "Jon Peltier" wrote in message ... 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 ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Geeves wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform Import problem | Excel Discussion (Misc queries) | |||
Userform problem? | Excel Discussion (Misc queries) | |||
userform problem | Excel Discussion (Misc queries) | |||
Userform problem (Listindex) | Excel Discussion (Misc queries) | |||
UserForm Recall Problem | Excel Discussion (Misc queries) |