VBA create Textbox and fill it with text out of a function

[tums]

Hello everybody,

I'm using Excel 2003 and like to write a VBA function which creates a textbox and fill it with some text.

My function looks like this (just the code which is relevant for the creation of the textbox):

Function CreateBox()
   Dim wsActive As Worksheet
   Dim box As Shape

   Set wsActive = ActiveSheet
   Set box = wsActive.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 100, 10)
   box.TextFrame.Characters.Text = "test"

End Function

When I execute this function from a cell (e.g. A1 = "=CreateBox()") the textbox is created but no text is visible.

After some time I found out that if I copy the code to a Sub() it works fine:

Sub testsub()

   Dim wsActive As Worksheet
   Dim box As Shape

   Set wsActive = ActiveSheet
   Set box = wsActive.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 100, 10)
   box.TextFrame.Characters.Text = "test"

End Sub

BUT it doesen't work if I call this Sub from a function:

Function testFunct()
   Call testsub()
End Function

What am I doing wrong?

 

[mikerickson]

When a function is called from a spreadsheet formula, there are limits on what it can do.

In general, changing the environment (formatting cells, putting formulas in cells, etc.) won't happen when called from the spreadsheet, although calling the function from a VB routine will effect those changes.

I'm not surprised that your text didn't get into the box. I am surprised that the text box was created.

You are doing nothing wrong. That's the way Excel is built. (Also some VB things like Range.Find don't work when part of a function called by the spreadsheet)

The way that I think of it is that Functions return values. That's all they do, return a value. If you want stuff written into cells, controls created or filled..... use a Sub. While this is not strictly enforced when functions are called from VB routines, it is when they are used in spreadsheet formulas.

 

[tums]

Thank You for the explanation mikerickson.

I think using a Sub is not possible in my case because I want to realize a function called drawrectangle(percent as range) which I can call from a specific cell and pass another cell as an argument. The result should be a rectangle with the value of the argument next to it. Drawing the rectangle according to the argument and placing a textbox next to it works fine. The only thing which does not work is filling the textbox with the value of the argument (I guess for the reason mikerickson has explained above). I've also tried to put the code for the creation of the textbox in a seperate Sub() and call that Sub from my function with the same result.

Is there anotherway how I can realize that?
http://www.mrexcel.com/forum/member.php?u=73026

 

[mikerickson]

Put the dimesions of the rectangle in cells. Create a button that calls a sub. That sub reads those cells and creates the rectangle.

One could write a Worksheet_Change event to change the size of an existing rectangle based on cell entries.

 

[tums]

If I understand your proposal correctly it would only be possible to draw one rectangle with one button. This is not practicable for my problem too. To complete the description of my goal (sorry I should have done it in post #1):

I have a column of numbers on the left and I'd like to draw the corresponding rectangles next to them. My original idea was to write a function as described, write it in the cell next to one value and copy it downwards.

Example:
A1 = 4 B1 = drawrectangle(A1)
A2 = 3 B2 = drawrectangle(A2)
...

Result:
Rectangle + number 4 on its right side
Rectangle + number 3 on its right side

I'd be grateful for any suggestions how to realize it

 

[imav]

If I understand your proposal correctly it would only be possible to draw one rectangle with one button.

You can have your command button draw rectangles based on any number of predetermined criteria .

What I believe mikerickson is trying to say is you have a listbox that you fill with an array and from that array you select your string (ex:A1)

Then hit your button and have the desired rectangle generated.