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

 

[Domenic]

The syntax for your formula is correct. How is your named range CS defined?

 

[woodnathan]

The syntax for your formula is correct. How is your named range CS defined?

CS, I believe, was originally defined as by selecting the area and typing in the named range.

Under the name manager it is shown as :

='Cross Section'!$B$3:&K$41

Again, this works on most of pour machines. Only one coworker is experiencing this issue and it only start this week.

 

[Domenic]

The syntax is correct.

And even though you're referencing a multi-cell range, it simply picks up the first cell and ignores the rest, so that should be okay.

Does the workbook in fact contain a worksheet called "Cross Section"?

When the error occurs, which line get's highlighted? And what is the exact error message displayed?

 

[woodnathan]

The syntax is correct.

And even though you're referencing a multi-cell range, it simply picks up the first cell and ignores the rest, so that should be okay.

Does the workbook in fact contain a worksheet called "Cross Section"?

When the error occurs, which line get's highlighted? And what is the exact error message displayed?

The main thing that is throwing me for a loop is that it works on other computers.

The sheeot "Cross Section" Exists.

When the spreadsheet is first loaded, the picture is already assigned to CS and the display works correctly. (this displays a cross-section at different elevations.)
If the line Sheets("Input").Shapes("CS_View").DrawingObject.Formula = "CS" runs, there is no error, but the display stops functioning. I can see CS in the formula bar, but it doesn't update to show the cross-section when the elevation is changed.

It kicks the error when it tries to set the picture formula to an empty set:
Sheets("Input").Shapes("CS_View").DrawingObject.Formula = "" gets highlighted.

The error is:
Run-time Error '1004': "Unable to set the formula property of the picture class"

 

[Domenic]

In your last post, you have...

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

As you can see, the equals sign (=) is missing. It should be...

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

...just like your original post.

 

[woodnathan]

I apologize, this is first time I've posted on this forum and am just now figuring out how to format the code blocks.

That was a typo, the code from the macro uses the "=":
Sheets("Input").Shapes("CS_View").DrawingObject.Formula = "=CS"

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Sudden issue with: DrawingObject.Formula = ""
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[woodnathan]

Apologies. I'm learning.

Cross-posted at:

https://www.reddit.com/r/excel/comments/17gi5f7

Sudden issue with: DrawingObject.Formula = ""

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...

www.excelforum.com

 

[Domenic]

You say that "...another macro draws a picture inside a range named "CS". Then you assign the named range to your shape. But in doing so, I don't think it would pickup the picture inside the named range, only the value from the first cell.

Anyway, it might help if you posted the code that draws a picture inside the name range "CS".