Dynamic Chart in Word using VBA???

Hi guys,

Great looking forum, just found it today when trying to get an answer to this question...

How do you make a graph/chart in word that can be edited using VBA.

I would like a graph that a user can make changes to the data using vba textboxes, and maybe change the graph type using tickboxes (pie/line/etc)

-- One step at a time though if somone could just tell me how to actually just change the value of a "microsoft graph chart" datasheet, i will prob be able to work the rest out myself.

Thanks for any help.

Regards. :thanx:

Re: Dynamic Chart in Word using VBA???

Thanks for your reply,

But the example in the search doesnt work for me.

I used your example macro on this thread: http://www.ozgrid.com/forum/showthread.php?t=32873

But when i run it, i get an error from this line:

Set objMSGraph = ActivePresentation.Slides(1).Shapes(3).OLEFormat.Object.Application

-- Runtime error '424' Object Required.

Heres what i did:

Added the graph object lib to the references in VBA.
Then made a graph by "insert -> Object -> Microsoft Graph Chart"
Pasted your example code.
Ran it. Got the above error.

I think the problem could be as simple as i dont know how to give the graph a "Name" -- like from "DEFAULT TEXBOX1" to "inputName" type thing.

Any more help would be great thanks.

Re: Dynamic Chart in Word using VBA???

Thanks for you help andy, but that just produces another error:

"The index into the specified collection is out of bounds"

I dont have any thing to post to show you, as im just working with an empty word doc.

Here is what i have tried so far:

http://premium1.uploadit.org/itsall3//Doc1.doc

Thanks again andy.

Re: Dynamic Chart in Word using VBA???

yay! Thanks andy, that works great.

Now i just have to work it out.

----
edit:

Got it matey, thanks so much, I can now change the differnt lines.

Just need to work out how to change the graph view from within VBA.

Anyway, thanks again andy, you've been really helpful.

Cheers

Re: Dynamic Chart in Word using VBA???

Ok, i kind of get how it works now, but i've got 2 problems really. :?

I cannot update a chart while the doc. is protected. -- this is somthing i would really need to do. I'm guessing theres a way round this, maybe unprotect the doc with a macro, then protect it again after update.

And another problem is that i need to update rows without knowing the previous value.

Right now its:

= Replace(objMSGraph.Object.Application.DataSheet.Cells(lngRow, 2), "value1", "value2")

where "value1" is where what it looks for to replace (if im wrong about this let me know, its your code matey, im just saying what i think i've worked out :thanx: )

I need a way to change the value of "value1" without knowing it. like "row1 = 10"

Cheers for your help again andy, you've saved me today :thanx:

Re: Dynamic Chart in Word using VBA???

Is there anyway to do somthing like this?

Var1 = objMSGraph.Object.Application.DataSheet.Cells(lngRow, 2).text

Just to get the value in the cell,

Or is that hoping too much? :roll:

Cheers.

Re: Dynamic Chart in Word using VBA???

arrr, i spent ages trying that and it didnt work, then when you showed me it, i realised what i was doing, i was putting the line above " lngRow = 2" - d'oh

Wow, you've been great mate, never had show much help on the net before.

I tried your advice about using excel to record macros and see how they work, they work great in excel, but moving them over to word, aint to great.

Like this:

ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.ChartType = xlPie

A nice easy way to change the chart from line graph into a piechart.

Does it work in word? nope. :roll:

You'd think it would be the same all the way through the office suite.

Cheers for your help. :thanx:

Re: Dynamic Chart in Word using VBA???

haha, you are a legend man, thanks so much.

I wish i could paypal you a pint, i've been trying to do this for 2 days before i found this forum.

:thanx: :thanx: :thanx:

All the best matey.

Cheers.