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]

I can change the formula =$A$1 but I cannot remove it.

 

[Domenic]

If the shape is Locked and the worksheet is protected, then you wouldn't be able to change or delete it, whether manually or by code. But the fact that you're able to change it, but not remove it, is somewhat perplexing.

 

[emmentaler]

I have used this code in a protected worksheet for 10 years and it has worked:
Sheets("KB Graphics").Shapes("Image1").DrawingObject.Formula = ""
As of last week, the code no longer works for 30% of my students, but it works for the others.

 

[emmentaler]

and yes: changing is possible, removing is not...

 

[Domenic]

When protecting the worksheet...

1) if "Edit objects" is selected/checked, you'll be allowed to make changes, or

2) if both "Locked" and "Locked Text" are de-selected/unchecked under Properties (right-click shape, and select Size and Properties), you'll also be allowed to make changes

 

[emmentaler]

It doesn't work even on unlocked objects, so I don't need to try it on locked ones....

 

[emmentaler]

and it doesn't work on unprotected worksheets

 

[Domenic]

Well, let's see if anyone else here has any other ideas . . .

 

[emmentaler]

Thank you for your commitment

 

[daisySun]

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

I have this exact same problem - it works for me, but not for a colleague (until last week it also worked fine for them)

Have you found any solution?