I have been looking at the Office Web Components and was thinking they need some publicity among Ozgrid Excel users.
The Office Web Components (OWC) are a series of components that you can add into a web page or any Office application. In this post I will be looking at adding a chart component to an Excel Userform.
One advantage of using a chart component rather than using the normal chart object is that you can put it on a userform. There is a problem though - it is not easy to reference data from a worksheet like you do with the normal chart object.
Below demonstrates how you can use the OWC Spreadsheet component to take the data from a normal worksheet and pass it to the chart control.
I am using Office 2003 which comes with OWC11 but with a little editing you should be able use the same techniques with earlier versions.
You need to create a new userform (from the VBE Insert > Userform) and add both a OWC spreadsheet control and a OWC chart control to the userform. Click the toolbox icon > Right click the tool box > additional controls > then check Microsoft Office Spreadsheet 11.0 and Microsoft Office Chart 11.0. The icons should now appear on the tool box and you can drag them on to the userform.
After you have entered your data into a normal worksheet you can build the userform initalisation event to copy the data from the worksheet to the spreadsheet control and then set the spreadsheet control as source for the chart control. You can then set the data in the chart using the same cell references as in your worksheet.
- Private Sub UserForm_Initialize()
- Dim ChtSpc As OWC11.ChartSpace
- Dim cht As OWC11.ChChart
- Dim Sps As OWC11.Spreadsheet
- Dim ws As Worksheet
- Set ChtSpc = Me.ChartSpace1
- Set Sps = Me.Spreadsheet1
- Set ws = ThisWorkbook.Worksheets("ChartData") ' change to you worksheet name
- Sps.Range("A1:Z1000") = ws.Range("A1:Z1000").Value ' Set worksheet range to sheet control range
- Set ChtSpc.DataSource = Sps ' set sheet control as chart control source
- Set cht = ChtSpc.Charts.Add ' Add blank chart
- ' Set data for chart
- With cht
- .SetData chDimCategories, 0, "A4:A15" ' change to your category range
- .SeriesCollection(0).SetData chDimValues, 0, "B4:B15" ' change to your series 1 range
- .HasLegend = True
- .SeriesCollection(1).SetData chDimValues, 0, "C4:C15" ' change to your series 2 range
- .HasTitle = True
- .Title.Caption = Sps.Range("A1") ' change to your title cell
- .Title.Interior.Color = 1677215
- .Type = chChartTypeColumn3D
- End With
- ' add some fancy shading
- ChtSpc.Interior.SetTwoColorGradient chGradientFromCenter, chGradientVariantEnd, 9125192, 16777215
- ' hide the sheet control
- Me.Spreadsheet1.Visible = False
- ' set the height of the chart control
- Me.ChartSpace1.Height = 480
- End Sub
In the above example I have copied the range("A1:Z1000") across. Given that most people live in this range you can then copy this code as is, and just change the "Set data for chart" section to your ranges.
Attached is an example workbook. It is based on OWC11 so you need Office 2003 to use it. You can download OWC11 at :- http://www.microsoft.com/downl…FD290D4B76&displaylang=en
In the example workbook have a look at the data in the worksheet (ChartData) then press the command button (Click This). A userform should appear with the chart component on. Remember this only works if you have Office 2003. Click the rotate button then right click the chart and select "commands and options", then select the 3D view and play with the slide bars.
In the next post I will try and demonstrate using OWC with OLAP.
Thanks to XL Dennis and Will Riley for lighting the way.