Sudden issue with: DrawingObject.Formula = ""

[woodnathan]

We have several spreadsheets that use VBA to assign a named range to a shape. Basically, another macro draws a picture inside a range named "CS". Then, the chunk of code below assigns the named range to the shape. A picture of the range will show in the shape. This has worked forever. In fact, it still works on my computer. But on my coworkers machine, it has suddenly started kicking out a '1004' error when it tries to set the DrawingObject.Formula = "".

I've checked it on other computers in the office and it works on theirs.

If I select the shape and type =CS into the formula bar, I don't get an error, but the picture doesn't update anymore.

If I select the shape and delete what is in the formula bar, (setting the formula value to ""), I get an error.

Again, this works on everyone else's computer. I've tried every setting in Options I can think of and checked references, but I'm stumped.

Figured I'd take it to the masses.

Offending chunk of code:

--------------------------

Private Sub Worksheet_Activate()

Sheets("Macro References").Range("A5").Value = 0

Sheets("Input").Shapes("CS_View").DrawingObject.Formula = "=CS"

End Sub

--------------------------

Private Sub Worksheet_Deactivate()

Sheets("Input").Shapes("CS_View").DrawingObject.Formula = ""

End Sub

 

[emmentaler]

The problem is independent of VBA code, formulas that are assigned to DrawingObjects can now be changed but not deleted. It's even crazier: Last week it worked again (on many different PCs) and I was happy, this week it doesn't work again on the same devices.

 

[vzczc]

This is, in essence, the same issue I have raised here:

Excel Vba, Picturelink, Changes in Excel Version 2310

I just noticed a breaking change after updating to Version 2310 today (Version 2310 (Build 16924.20124 Click-to-Run)) If you have a picturelink in Sheet1 with the name pictureOne where the formula property is set to "=A1:A10" In previous versions, you could do the following in vba...

www.mrexcel.com

In this thread I describe how this issue was raised with MS Support, but they were not able to help at all.

Until I recently noticed this change from MS.

Release notes for Current Channel releases - Office release notes

Provides IT Pros with release notes for Monthly Channel releases for Microsoft 365 Apps.

learn.microsoft.com

A partial fix was released by Microsoft in version 2403 (Build 17425.20138). This removes the error when setting the formula property to "", but if you then set the formula back to refer to a range, the link is no longer dynamic.

I use this type of functionality extensively for dashboards that summarize complex workbooks.

The only way I have found to get around this (other than recreating the linked picture from scratch which you can do using copy/paste (described earlier in my thread, is to select the object, press F2 to enter the formula bar, and then enter. After this the link is dynamic again.

So to do this in code you could do something like (replace sheetname and picture/shape name with whatever you are using), It is a bit ugly as it uses .select and sendKeys which I normally never do, but I have not found another way to do it.

To fix this properly, Microsoft needs to update Excel to work as it did before October 2023

    Application.ScreenUpdating = False
    Sheets("Sheet1").Pictures("ThePicture").Formula = "=theRange"
    Sheets("Sheet1").Pictures("ThePicture").Select
    Application.SendKeys "{F2}{ENTER}{ESC}", True
    Application.ScreenUpdating = True