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]

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!

Can you post your current code - as last time you weren't even using the method that pgc01 had suggested.

 

[laughingdevil]

Can you post your current code - as last time you weren't even using the method that pgc01 had suggested.

Indeed, I am changing the code commenting in and out the methods as requested by your good selves. This is the block I was refering too in my previous statment

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

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

as I said the first literally appears to do nothing when stepping through the code, then it hits the second bit, which works fine.

 

[GlennUK]

OK, you have the same as me, codewise ... what version of Excel are you using? What is xls defined as? Is the sheet that xls refers to the active sheet?

 

[laughingdevil]

OK, you have the same as me, codewise ... what version of Excel are you using? What is xls defined as? Is the sheet that xls refers to the active sheet?

Excel is 2007 (sorry should have mentioned that before)

declaration for xls is

Dim xls As Excel.Worksheet

This is then set to a different worksheet in the workbook in a loop of which my code is a part (in short there are about 20 worksheets which the loop quite merily goes around sticking data it gets from SQL into different cells before then copying the graphs over to word)

When I am running it ATM it is the active sheet, but the code is designed for any sheet too be it, hence why I don't want .selects because I don't have any anywhere else. However to give you a bit of context the below line of code is right before my previous block

"xls.Shapes.Range("dq_poor_ovalfu").Fill.ForeColor.RGB = RGB(243, 215, 45)"

That works quite happily so I am assuming it is "active" but in case it's not before I run the code I am manually activating it.

 

[GlennUK]

Your code actually isn't the same as mine:

mine is:
xls.Shapes("dq_poor_ovalfu").OLEFormat.Object.Formula = "=dq_moderate"

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

... remove the ".Range" and try again.

 

[laughingdevil]

Your code actually isn't the same as mine:

mine is:
xls.Shapes("dq_poor_ovalfu").OLEFormat.Object.Formula = "=dq_moderate"

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

... remove the ".Range" and try again.

flip I didn't even notice that!

Tada! It works!

Now of course the question is why? why do all my other formatting lines work with ".range(" and this one only works without!

 

[GlennUK]

Hooray!!!

I'd guess that the Range qualifier is from long long ago, and the OLEFormat part is a more recent addition ( like since OLE components started to be included in Excel ), and that the two just aren't compatible. Phew, who'd have thought it

 

[laughingdevil]

Hooray!!!

I'd guess that the Range qualifier is from long long ago, and the OLEFormat part is a more recent addition ( like since OLE components started to be included in Excel ), and that the two just aren't compatible. Phew, who'd have thought it

That makes sense! Hmm I wonder if I still need the .ranges on the other lines?

Hmm, turns out you don't!

Anyway thanks for all your help on this one, issue "resolved"