Hi all...
not new to programming, but I'm rather old school... back when line numbers
were in vogue ;-)

Anyway, this newfangled object stuff sometimes throws me.

So here I am... trying to make the Microsoft Office Chart Component work
from within an Excel Userform. Don't try to talk me out of it :-)

Anywhoo... I am having success, but am at an impasse...

I need to set the DataSource for the chartspace to a spreadsheet object.
Some Microsoft example code says I should be able to do it, but as yet, I am
always getting a "type mismatch error" whenever I come to this line:

Set ChartSpace1.DataSource = Spreadsheet1

or

Set ChartSpace1.DataSource = ActiveSheet

or
...

or
...

or

... any of the numerous variations I've tried to point to the
spreadsheet I am using (in the same project as the userform).

Apparently, the object that Chartspace1 needs as a datasource is not the
same type of object as the excel spreadsheet... or perhaps I'm not doing it
right syntactically.

I'm Using Excel 2002...

If anyone could tell me how to set the datasource to my active sheet, I'll
buy you a beer.

All the best,

Mike

Er,

I'm no expert either, but since no-one has yet joined the fray...

I think the DataSource is expecting a range.

So try something like:

ActiveSheet.Range("A1:G1")

Probably won't work knowing me.

Graha

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Mike -

Does it have to be a live chart on the form? John Walkenbach
(http://j-walk.com) has a technique to put a picture of a chart on a
form; you can update the picture as often as you like. Also, Stephen
Bullen (http://bmsltd.co.uk) has a way to paste the chart's picture onto
the form. Either of these must be lots easier to do than muck about in
the OWC controls.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

rci wrote:

Hi all...
not new to programming, but I'm rather old school... back when line numbers
were in vogue ;-)

Anyway, this newfangled object stuff sometimes throws me.

So here I am... trying to make the Microsoft Office Chart Component work
from within an Excel Userform. Don't try to talk me out of it :-)

Anywhoo... I am having success, but am at an impasse...

I need to set the DataSource for the chartspace to a spreadsheet object.
Some Microsoft example code says I should be able to do it, but as yet, I am
always getting a "type mismatch error" whenever I come to this line:

Set ChartSpace1.DataSource = Spreadsheet1

or

Set ChartSpace1.DataSource = ActiveSheet

or
...

or
...

or

... any of the numerous variations I've tried to point to the
spreadsheet I am using (in the same project as the userform).

Apparently, the object that Chartspace1 needs as a datasource is not the
same type of object as the excel spreadsheet... or perhaps I'm not doing it
right syntactically.

I'm Using Excel 2002...

If anyone could tell me how to set the datasource to my active sheet, I'll
buy you a beer.

All the best,

Mike

Jon Peltier wrote:

Hi Jon,

yes, it does need to be live... I'm already using JWalks gif technique...
but I need to upgrade it to a live chart. I am finding that it's not all
that difficult to do and can change everything about the chart
programatically... no big deal.

So I know I'm really clos except for being able to understand what a
"spreadsheet object" is so that I can sucessfully set it as the datasouce.

How does one reference a given worksheet as a "spreatsheet object"?

Thanks,

Mike

: Mike -

: Does it have to be a live chart on the form? John Walkenbach
: (http://j-walk.com) has a technique to put a picture of a chart on a
: form; you can update the picture as often as you like. Also, Stephen
: Bullen (http://bmsltd.co.uk) has a way to paste the chart's picture onto
: the form. Either of these must be lots easier to do than muck about in
: the OWC controls.

: - Jon
: -------
: Jon Peltier, Microsoft Excel MVP
: http://www.geocities.com/jonpeltier/Excel/index.html
: _______

: rci wrote:

: Hi all...
: not new to programming, but I'm rather old school... back when line numbers
: were in vogue ;-)
:
: Anyway, this newfangled object stuff sometimes throws me.
:
: So here I am... trying to make the Microsoft Office Chart Component work
: from within an Excel Userform. Don't try to talk me out of it :-)
:
: Anywhoo... I am having success, but am at an impasse...
:
: I need to set the DataSource for the chartspace to a spreadsheet object.
: Some Microsoft example code says I should be able to do it, but as yet, I am
: always getting a "type mismatch error" whenever I come to this line:
:
: Set ChartSpace1.DataSource = Spreadsheet1
:
: or
:
: Set ChartSpace1.DataSource = ActiveSheet
:
: or
: ...
:
: or
: ...
:
: or
:
: ... any of the numerous variations I've tried to point to the
: spreadsheet I am using (in the same project as the userform).
:
: Apparently, the object that Chartspace1 needs as a datasource is not the
: same type of object as the excel spreadsheet... or perhaps I'm not doing it
: right syntactically.
:
: I'm Using Excel 2002...
:
: If anyone could tell me how to set the datasource to my active sheet, I'll
: buy you a beer.
:
: All the best,
:
: Mike
:
:

Thanks to all for the replies so far...

Here is a code sample that shows the way that the code does NOT work...



Private Sub ChartSpace1_DblClick()

Dim Spreadsheet1 As OWC10.Spreadsheet

Spreadsheet1 = Sheet1.Range("B5", "C10")

Set ChartSpace1.DataSource = Spreadsheet1

End Sub


Sending the Sheet1.range object to Spreadsheet1 gives me a type mismatch
error...

Supposedly, the OWC10.Spreadsheet object is a container that can hold
worksheet objects and other common excel objects.

Thanks for your help...

Mike



rci wrote:
: Jon Peltier wrote:

: Hi Jon,

: yes, it does need to be live... I'm already using JWalks gif technique...
: but I need to upgrade it to a live chart. I am finding that it's not all
: that difficult to do and can change everything about the chart
: programatically... no big deal.

: So I know I'm really clos except for being able to understand what a
: "spreadsheet object" is so that I can sucessfully set it as the datasouce.

: How does one reference a given worksheet as a "spreatsheet object"?

: Thanks,

: Mike

: : Mike -

: : Does it have to be a live chart on the form? John Walkenbach
: : (http://j-walk.com) has a technique to put a picture of a chart on a
: : form; you can update the picture as often as you like. Also, Stephen
: : Bullen (http://bmsltd.co.uk) has a way to paste the chart's picture onto
: : the form. Either of these must be lots easier to do than muck about in
: : the OWC controls.

: : - Jon
: : -------
: : Jon Peltier, Microsoft Excel MVP
: : http://www.geocities.com/jonpeltier/Excel/index.html
: : _______

: : rci wrote:

: : Hi all...
: : not new to programming, but I'm rather old school... back when line numbers
: : were in vogue ;-)
: :
: : Anyway, this newfangled object stuff sometimes throws me.
: :
: : So here I am... trying to make the Microsoft Office Chart Component work
: : from within an Excel Userform. Don't try to talk me out of it :-)
: :
: : Anywhoo... I am having success, but am at an impasse...
: :
: : I need to set the DataSource for the chartspace to a spreadsheet object.
: : Some Microsoft example code says I should be able to do it, but as yet, I am
: : always getting a "type mismatch error" whenever I come to this line:
: :
: : Set ChartSpace1.DataSource = Spreadsheet1
: :
: : or
: :
: : Set ChartSpace1.DataSource = ActiveSheet
: :
: : or
: : ...
: :
: : or
: : ...
: :
: : or
: :
: : ... any of the numerous variations I've tried to point to the
: : spreadsheet I am using (in the same project as the userform).
: :
: : Apparently, the object that Chartspace1 needs as a datasource is not the
: : same type of object as the excel spreadsheet... or perhaps I'm not doing it
: : right syntactically.
: :
: : I'm Using Excel 2002...
: :
: : If anyone could tell me how to set the datasource to my active sheet, I'll
: : buy you a beer.
: :
: : All the best,
: :
: : Mike
: :
: :

Hi Mike,

Sending the Sheet1.range object to Spreadsheet1 gives me a type mismatch
error...

Supposedly, the OWC10.Spreadsheet object is a container that can hold
worksheet objects and other common excel objects.

The Office Web Components are separate from Excel, though they are very
similar, and they can not be used a data sources for each other (as a
'live link'). Only the OWC Spreadsheet can be used as a data source for
the OWC Chart, so you're going to have to decide between two routes:

1. If you have the requirement for the user to interact directly with the
chart on the form (i.e. actually using the functionality of the OWC chart
control to change its appearance), you'll have to use the OWC chart on the
form, have a separate (probably invisible) OWC Spreadsheet control on the
same form and initialise the spreadsheet control by setting its XML (e.g.
create a template workbook in Excel, save it as XML, then open it with the
OWC Spreadsheet). This doesn't create a 'live' link, but just gets you
starting in the OWC Spreadsheet.

2. If the requirement is only to *display* a chart on a form, and you do
not need the user to be able to format that chart, it is much easier to
have a chart in Excel and display a picture of it on the form. You can
use VBA to modify the chart in response to user interaction on the form
and redsiplay the picture, to make it appear 'live' if you wish. The
PastePicture file on my web site has an example of this.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

Stephen Bullen wrote:

Hi Stephen... thanks so much for the info.


Actually, I've had quite a breakthrough. I have a working chart on a form
now using OWC!

First... I don't consider myself a programmer. I hack at it, so what I
present below is something that simply works, and I don't have the knowledge
to know exactly why.

The approach I use is to make spreadsheet in Excel with VBA. When I need to
display and control the chart on the form, I simply have an OWC spreadsheet
object that I create that will accept a range from the Excel spreadsheet...

Compared to plopping gifs onto the userform, this is *fast*:

BEGIN CODE


Dim oChart
Dim Spreadsheet1 As OWC10.Spreadsheet
Dim c

Set oChart = ChartSpace1.Charts(0) 'point to our chart by index #

Set Spreadsheet1 = CreateObject("OWC10.Spreadsheet")
Spreadsheet1.Range("A1", "B650") = Sheet1.Range("A1", "B650").Value

Set c = ChartSpace1.Constants
Set ChartSpace1.DataSource = Spreadsheet1

ChartSpace1.Charts(0).SetData c.chDimCategories, 0, "a2:a650"
ChartSpace1.Charts(0).SetData c.chDimSeriesNames, 0, "b1"
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, 0,
"b2:b650"

oChart.HasTitle = True
oChart.Title.Caption = "My Title"


END CODE


Not to difficult... and it shows how easy it is to control other chart
elements like the caption.

I think the key for me was realizing that I could simply move as much of an
excel sheet over to an OWC sheet by way of a range.

All the best!

Mike




: Hi Mike,

: Sending the Sheet1.range object to Spreadsheet1 gives me a type mismatch
: error...
:
: Supposedly, the OWC10.Spreadsheet object is a container that can hold
: worksheet objects and other common excel objects.

: The Office Web Components are separate from Excel, though they are very
: similar, and they can not be used a data sources for each other (as a
: 'live link'). Only the OWC Spreadsheet can be used as a data source for
: the OWC Chart, so you're going to have to decide between two routes:

: 1. If you have the requirement for the user to interact directly with the
: chart on the form (i.e. actually using the functionality of the OWC chart
: control to change its appearance), you'll have to use the OWC chart on the
: form, have a separate (probably invisible) OWC Spreadsheet control on the
: same form and initialise the spreadsheet control by setting its XML (e.g.
: create a template workbook in Excel, save it as XML, then open it with the
: OWC Spreadsheet). This doesn't create a 'live' link, but just gets you
: starting in the OWC Spreadsheet.
:
: 2. If the requirement is only to *display* a chart on a form, and you do
: not need the user to be able to format that chart, it is much easier to
: have a chart in Excel and display a picture of it on the form. You can
: use VBA to modify the chart in response to user interaction on the form
: and redsiplay the picture, to make it appear 'live' if you wish. The
: PastePicture file on my web site has an example of this.

: Regards

: Stephen Bullen
: Microsoft MVP - Excel
: www.BMSLtd.co.uk

Dear rci,

Thank you so much for your path-breaking work with OWC charts on Exce
user forms. You saved me hours of agony. Because I now view you as a
expert on all things OWC, would you please let me know if and ho
you’ve handled the following (incidentally, I am working with OWC1
objects, not OWC10, but that shouldn’t matter much):

1. Exporting to Excel. The OWC Spreadsheet includes a nifty “Export t
Excel” button on its toolbar. The OWC Chart does not. Have yo
discovered a corresponding chChart command somewhere that I hav
overlooked?

Also, if you place an interactive OWC spreadsheet on a form (ver
sexy), the toolbar’s export button always exports to a new applicatio
of Excel, not the one containing the user form with the OW
spreadsheet. So you wind up with 2 open Excel applications instead o
one. Have you encountered this yourself and, if so, any ideas abou
working around it.

2. Chart formatting seems a bit quirky. There’s no problem, fo
example, wit
oChart.SeriesCollection(0).Scalings(c.chDimValues) .Maximum or .Minimum
but I haven’t found a way to apply .MajorUnit or .MajorTickMark
without encountering an error 438 (property or method not supported b
object). Do you have any code snippets where you actually got thes
formatting features to work? I’m beginning to think the Object Browse
is lying – that these properties don’t really exist at all.

3. Have you figured out a way to work with defined ranges (anythin
equivalent to “Set myRange = .Range(.Cells(a,b), .Cells(c,d))”) in a
OWC Spreadsheet. I don’t mind converting everything to an “A1” styl
string, but it’s not very elegant.

Once again, I can’t thank you enough for persevering on the issue o
actual “live” chart objects on forms. I had been using “live
spreadsheets on forms for quite some time, and was waiting for someon
to figure out interactive charts. You’ve made a lot of our live
easier. Thanks.

Pat Finegan
:

--
Message posted from http://www.ExcelForum.com