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

 

[woodnathan]

Let me be clear, this macro fully functions on mine and others machines. The code has been written for years and has worked flawlessly for as long. Until Monday of this week, did the co worker having the issue was able to run this code.

Could this possibly be a permission issue for the files on the network drive?

 

[Domenic]

Could this possibly be a permission issue for the files on the network drive?

No, not if you're simply assigning a range to your shape via .Formula . . .

 

[emmentaler]

I have exactly the same problem and can't find an explanation...

 

[emmentaler]

Manual deletion of the formula in the Formla Bar is also not possible, so I suspect that the error is not in the code, but in a changed basic setting of Excel - but I cannot find a solution.

 

[emmentaler]

A formula that belongs to a graphic can no longer be deleted manually - how can this be remedied?

 

[Domenic]

Does the same thing happen when using a brand new workbook?

If not, maybe the workbook itself is corrupt. In this case, try repairing it...

Repair a corrupted workbook - Microsoft Support

Recover a corrupted workbook manually when Excel can't start File Recovery mode automatically and learn about saving backup and recovery files to prevent losing your work.

support.microsoft.com

If the same thing happens in a new workbook, try repairing Office 365...

Repair an Office application - Microsoft Support

Repair broken Office app installations.

support.microsoft.com

Hope this helps!

 

[emmentaler]

I have tried both - without success

 

[Domenic]

If you don't have that same problem with a brand new workbook, then it's likely that the workbook is corrupt beyond repair...

 

[emmentaler]

The problem also exists in a completely new workbook

 

[emmentaler]

I can change the fomrel =$A$1 zar but not remove it.