ExcelDashboardWidgets

PS: I just wrote a bit of VBA to programmatically change the formula in a shape from =a2 I has set via Excel interface to =a1 to see if this also trigger the format change of the shape. It did! My shape had a format of red font and size 40 and when I executed the Shp.DrawingObject.Formula = "=A1" statement it change to black and size 11, being the format of cell A1. I also had this in the Watch window. I can send a screenshot to you if you like, but it simply going to confirm what I have been experiencing. I have also placed a forum post on Mr Excel for this. Will see what comes of that.

Sub FormApp()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
'formula
Shp.DrawingObject.Formula = "=A1"
Next
End Sub

Hey suthosharko

Interesting!! Can we trouble you to upload the file (with the macro - exactly how you have it set up) and we'll run it on our machine to see if the results are the same as yours or different!!

Cheers

DashboardWidgets

Here it is

Any thoughts? Really keen to get to the bottom of this.

Hi Suthosharko

We ran your macro - here are the results!



As you can see, we didn't experience any formatting losses. We're also still a little stumped on what you need to do to solve this problem on your machine.

The only solution we can propose at the moment is to try the Excel 2013 trial version? We would be very surprised if you see the same behaviour there.

Sorry we can't be more helpful - we will still keep looking out for a solution.

Cheers

DashboardWidgets

OK thanks Dashboardwidgets,

I will investigate further. This issue is driving me around the bend though.

Hi Suthosharko

Just a quick idea - if the widgets are resetting to match the font settings in the originating cell - would it help if we updated all the widgets so that the formatting of all the blue boxes (on the 'My Calculations Page') matched the correct formatting for the widget textboxes?

We can send you an example if you like (let us know which widget to try).

Sorry that this is making you crazy - there are Gremlins living in Excel - we can show you more examples if you like - try pasting a widget into a new instance of the Excel program (i.e. START > EXCEL > NEW) and a brand new workbook opened from an existing instance of Excel (i.e. FILE > NEW > CREATE). You'll notice different formatting!!

Let us know if you think the first idea (updating all the font formats on the blue cells) will work for you.

Cheers

DashboardWidgets

Hi Dashboardwidgets,

That's ok ... if I need to revert to that technique then I'd do that myself. I am pursuing other excel forums to see what there experience is to see with shape formulas to try and pinpoint why my Excel 2010 version is not behaving like yours and obviously everyone else's using the widget. Still baffles me that I have had this on 2010 on two of my laptops and on a colleagues.

Hello ExcelDashboardWidgets,

I posted about this problem on the MS Excel forum (http://answers.microsoft.com/en-us/offi ... 796c4c4073) and received the following reply from the forum moderator:

Hi,
I've tested this in E2003, e2007 and e2013, I don't have E2010 to hand right now but believe it to be the same, and they all behave in the same way.
1. Put a shape on a worksheet.
2. Enter some text in (say) a1 and colour the font (say) Red.
3. select the shape
4. Click in the formula bar and type =A1
5. Tap enter and the shape is populated with the text in a1 and picks up the red colour.
6. Changing the font colour of A1 does not cause the colour of the text in the shape to change.
7. Force Excel to calculate by tapping F9 or by entering another formula in a cell and the font colour still isn't picked up I the shape.
8. Select the shape and click in the formula bar and tap enter and the font colour in the shape updates to the same as the font colour in A1.
AFAIK there is no other way [to stop this behaviour] except by resorting to VB code.

This is what I experience on e2010 ... as soon as the formula is changed the shape's format becomes the same as the referenced cell, so I have asked if anyone else on that forum can independently verify that this also does occur with e2010 as he did not test that version. So based on this I still cannot see how you don't have the same behaviour and thus the same problem of the format changing when you change the formula in e2010.

Any ideas?

Hi Suthoskarko

We carried out your test - take a look at the video below...

One last thing - if you paste a widget (for example the circular dial) into a new workbook, do you get the same behaviour as if you paste the same widget into the current open workbook?

Cheers

DashboardWidgets