Help needed with Shapes Formulas

[laughingdevil]

Hi

I need some help if possible, and for the first time I can't find what I need in previous posts on this site!

I am working on a report that includes some "thermometers" that show the quality of the data collected, I'm adding lots of new funcunality to somthing developed by my predecessor, who did this by having a different shape for each data quality "band" and selecting that from excel and copying it to word, however there are now so many it's getting silly, so I'm working on having one shape and dynamically altering it to show what I want.

I've got the chart colour shape colour etc to change dynamically, so am almost there, what I need to do now is to change the formula that is associated with the shape (note this is not for the shapes textbox, but for the shape itself), so by default when you click on the shape the formula bar has "=dq_poor" in it, which I need to change, I've tried using code similar to what I've found for textboxes and cells to no avail. The line I'm using is this

" xls.Shapes.Range("dq_oval").Formula = "dq_moderate" "

This sort of code works well for other things I mentioned above, but not this, any help would be appreciated.

I've tried to be as complete as I can be, but feel free to ask any q's you need to to get to the bottom of this.

 

[GlennUK]

It's be something like:

    ActiveSheet.Shapes("Right Arrow 2").Select
    Selection.Formula = "=$K$1"

 

[laughingdevil]

It's be something like:

    ActiveSheet.Shapes("Right Arrow 2").Select
    Selection.Formula = "=$K$1"

Thanks, one problems solved, I was missing the "=", (that was silly of me!) however is there no way to do it directly? I really don't using .select and then selecton...= as it tends to slow things down and leave all sorts of possible things that can go wrong (ie if the user makes a different selection between the 2 lines of code)

 

[pgc01]

Hi

Try:

ActiveSheet.Shapes("Right Arrow 1").OLEFormat.Object.Formula = "=$A$1"

 

[laughingdevil]

Hi

Try:

ActiveSheet.Shapes("Right Arrow 1").OLEFormat.Object.Formula = "=$A$1"

That doesn't seem to work at all, and by that I mean when stepping through the code it doesn't change the formula for the shape, there are no error messages.

The selection.formula ="=...." does work, I'd just like to know if there is a way of doing it without the selection but via direct assignment.

 

[pgc01]

I tested in excel 2010 and it works for me.

Can you post your version?

 

[GlennUK]

It works fine in Excel 2007 too, just for info.

 

[laughingdevil]

This works (ie when I then step through and click on the shape the formula bar says = "dq_moderate"

"xls.Shapes.Range("dq_poor_ovalfu").Select
Selection.Formula = "=dq_moderate""

This does not (literally nothing happens excel steps over the line as if done but nothing has changed)

"xls.Shapes.Range("dq_poor_ovalfu").Formula = "=dq_moderate""

 

[GlennUK]

and what about:

xls.Shapes.Range("dq_poor_ovalfu").OLEFormat.Object.Formula = "=dq_moderate"

?

 

[laughingdevil]

and what about:

xls.Shapes.Range("dq_poor_ovalfu").OLEFormat.Object.Formula = "=dq_moderate"

?

Again that does nothing, In my test just now I inserted that before the .select way, stepped through it, the above did nothing, then it did the select, then it did the select.formula, and it changed. Most random!