Excel: works in immediate not in function

[mintjulep]

This is driving me nuts, obviously I am missing something simple.

The following code creates an autoshape circle, and exits without error.

Public Function drawlabel(tag As String)
Worksheets("sheet4").Shapes.AddShape(msoShapeOval, 100, 100, 40, 40).Name = tag
Worksheets("sheet4").Shapes(tag).TextFrame.Characters.Text = "test"

End Function

But the autoshape has no text inside it.

However I can enter the exact same line (substituting the correct value for tag) in the immediate window, and "test" shows up inside the autoshape that was created when the function ran.

What am I missing? How do I properly access the .text property of an autoshape in a function?

 

[SkipVought]

Hi,

It works for me. However, I am NOT calling this from the sheet.

How are you calling this function?

Exactly where does the function reside?



Skip,

_[red][/red]

 

[mintjulep]

Skip,

The function resides in a module, along with several other functions that work just fine.

Up until I read your post, I have been calling the function from a cell in the worksheet, sheet4.

I just tried calling the function from the immediate window and it worked.

WTF?

 

[SkipVought]

There are some things that a spreadsheet function cannot do that will work in other places. Like, you can't use a function to modify a cell, other than the one the function is in.

Modifying the TEXT, apparently, is one of them.

Skip,

_[red][/red]

 

[mintjulep]

Oh, that is so annoying.

FWIW, it works if the functions is called from a userform.commandbutton

 

[SkipVought]

I know. That's why I asked the question I did.

Skip,

_[red][/red]

 

[mintjulep]

My resident guru figured out that if you call the function from a worksheet change event it works.