Solved: Graph inside a userform

[Maka]

Hi, Ive tried to use a graph inside a userform and the only way I have found it to be done is and indirect way by using GIF images an pasting them in the .image in the userform. however i read a way to insert a graph in tools, extras and microsoft office chart 11.0. I have not been able to work with it properly. Is there a way to link it to a graph inside a workbook, etc.? has anybody some info to guide me? thanks

[johnske]

Hi Maka,

If you have Office 2000 or greater you can do this using OWC (Microsoft Office Web Compoments) by first creating a spreadsheet on the userform and linking the userform chart to data on that spreadsheet. (Note that if you wish you can set the spreadsheet to Visible = False when the userform's shown).

Go to Tools > Additional Controls > Microsoft Office Chart 9.0 (this sets a reference to Microsoft Office Web Components 9.0). Add a Microsoft Office Spreadsheet 9.0 control to the user form.

NOTE: If the Microsoft Office Spreadsheet 9.0 (or Spreadsheet) control and the Microsoft Office Chart 9.0 (or ChartSpace) controls are not visible in the Control Toolbox, follow these steps...

1) Right-click the Control Toolbox and click Additional Controls.
2) Click both the Microsoft Office Chart 9.0 and Microsoft Office Spreadsheet 9.0 check boxes and then click OK...

This is the userform code example used in the attachment:
[vba]Option Explicit

Private Sub UserForm_Initialize()

'Spreadsheet1.Visible = False
With ChartSpace1
' Add a chart.
.Charts.Add

' Set the data source of the chart to the Spreadsheet control.
.DataSource = Spreadsheet1

With .Charts(0)
' Create a bar chart.
.Type = chChartTypeBarClustered

' Add two data series to the chart.
.SeriesCollection.Add
.SeriesCollection.Add

' Set the properties of the first data series.
With .SeriesCollection(0)
.SetData chDimSeriesNames, 0, "B1"
.SetData chDimCategories, 0, "A2:A5"
.SetData chDimValues, 0, "B2:B5"
End With

' Set the properties of the second data series.
With .SeriesCollection(1)
.SetData chDimSeriesNames, 0, "C1"
.SetData chDimValues, 0, "C2:C5"
End With

' Display the legend.
.HasLegend = True
End With
End With
End Sub

[/vba]

[Maka]

thanks, this is great ( i learned a new control ). Still, id there any way i can now link the spreadsheet to my worksheet. I already have a complex chart with lots of data and error bars all over it which now im able to handle in the chart (inside the userform) but so far i have only managed to copy all my data into the spreadsheet range by range and create the chart as you showed me. but can i relate the spreadsheet to my worksheet or anything alike? thanks a lot, so far its been very useful

[johnske]

thanks, this is great ( i learned a new control ). Still, id there any way i can now link the spreadsheet to my worksheet. I already have a complex chart with lots of data and error bars all over it which now im able to handle in the chart (inside the userform) but so far i have only managed to copy all my data into the spreadsheet range by range and create the chart as you showed me. but can i relate the spreadsheet to my worksheet or anything alike? thanks a lot, so far its been very useful

One way would be to use the userform initialize event to copy the data on your worksheets used range (or whatever range you choose) and paste it onto the spreadsheet, as in the example below and in the updated attachment.

To find out more about these controls, open the VBE window, click the spreadsheet control and press F1.

[vba] Option Explicit

Private Sub UserForm_Initialize()

'Spreadsheet1.Visible = False
With ChartSpace1

' Add a chart.
.Charts.Add

' Set the data source of the chart to the Spreadsheet control.
.DataSource = Spreadsheet1

With .Charts(0)

' Create a bar chart.
.Type = chChartTypeBarClustered

' Add two data series to the chart.
.SeriesCollection.Add
.SeriesCollection.Add

' Set the properties of the first data series.
With .SeriesCollection(0)
.SetData chDimSeriesNames, 0, "B1"
.SetData chDimCategories, 0, "A2:A5"
.SetData chDimValues, 0, "B2:B5"
End With

' Set the properties of the second data series.
With .SeriesCollection(1)
.SetData chDimSeriesNames, 0, "C1"
.SetData chDimValues, 0, "C2:C5"
End With

' Display the legend.
.HasLegend = True
End With

End With

'<< ADD DATA (assuming your data is on sheet2) >>
Sheets("Sheet2").UsedRange.Copy

With Application
.ScreenUpdating = False

With MyForm.Spreadsheet1.ActiveSheet
.Range("A1").Paste
.Range("A1").Select
End With

.CutCopyMode = False
.ScreenUpdating = True
DoEvents
End With

End Sub[/vba]

[Maka]

thanks, that's all i need

[johnske]

thanks, that's all i need

I'll take that to mean mark it solved

[releven]

Thanks for the help!

[bjsatola]

I've switched to a 64-bit operating system, and now the Chartspace control can no longer be added to the Toolbox? It doesn't even show up as an "additional control," which is needed to add it to the Toolbox; however, OWC11 can be referenced and scene in the object browser...

Has anyone tried to use OWC11 in Excel 2007/2010 on a 64-bit operating system?

[Gurduloo]

I've switched to a 64-bit operating system, and now the Chartspace control can no longer be added to the Toolbox? It doesn't even show up as an "additional control," which is needed to add it to the Toolbox; however, OWC11 can be referenced and scene in the object browser...

Has anyone tried to use OWC11 in Excel 2007/2010 on a 64-bit operating system?

I've run into the same problem. From what I can gather, the OWC11.dll library is not compatible with 64 bit software. In my case, I'm trying to display an OWC spreadsheet object on a user form in VBA with MS Office. It works in 32 bit office, but not with 64 bit office.

[jacksmith]

Nice information,Thanks for sharing informative post.