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

tums

New Member
Joined
Nov 9, 2008
Messages
3
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):

Code:
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:
Code:
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:

Code:
Function testFunct()
   Call testsub()
End Function
What am I doing wrong?
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,318
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.
 
Last edited:

tums

New Member
Joined
Nov 9, 2008
Messages
3
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,318
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

New Member
Joined
Nov 9, 2008
Messages
3
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
 
Last edited:

imav

New Member
Joined
Jun 27, 2011
Messages
21
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.
 

Forum statistics

Threads
1,181,649
Messages
5,931,217
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top