MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Inserting text in Textbox in Excel

  Asked By: Kuhaylah    Date: Jan 29    Category: MS Office    Views: 2458

I am using Excel 2003, SP1.

I have extensive macros in Excel that extracts data from an Oracle
database and creates multiple worksheets to display the data.
On the first sheet, there is a cell that should contain the "machine
number". If the user enters "See Note", the macro is to insert a
textbox that will display the heading: "Machines:" followed by the
list of machine numbers extracted from Oracle. Because I have to
supply the document number and revision in order to retrieve the data
from Oracle, I wrote it as a function. However, in testing, I
changed it to a subroutine and had the same results.

In a blank cell, I have the formula:
=IF(LEFT(O55,3) = "SEE",Get_Ora_Mach(),"")
(I removed the variables being passed for testing purposes)

The function looks like:

Function Get_Ora_Mach()
Dim CreateFlag
Dim BoxTop, BoxWidth, BoxLeft, BoxHeight
Dim BoxName, I
CreateFlag = True
Debug.Print "Count: " & Selection.ShapeRange.Count
' Look for existing textbox called "VAR_MACH"
For I = 1 To Selection.ShapeRange.Count
If (UCase(Selection.ShapeRange(I).Name) = "VAR_MACH") Then
CreateFlag = False
Exit For
End If
Next I
If (CreateFlag) Then
BoxLeft = 723
BoxWidth = 850 - BoxLeft
BoxTop = 885 - 20 * 4 '(testing: 4 lines in textbox)
BoxHeight = 885 - BoxTop

(msoTextOrientationHorizontal, _
BoxLeft, BoxTop, BoxWidth, BoxHeight).Select

BoxName = Selection.Name
ActiveSheet.Shapes(BoxName).Name = "VAR_MACH"
End If
Selection.Characters.Text = "MACHINES:" & Chr(10) & "246014"
& Chr(10) & "246016"
Get_Ora_Mach = ""
End Function
' When run from the Spreadsheet, The textbox is created, but
' the text is not inserted into the box.
' If I create a subroutine to call the function:
Sub add_txtbox()
Dim Result
Result = Get_Ora_Mah()
End Sub
' The function works as expected.
' The same happens if the texbox already exists

I intentionally have not used "forms" because of the volume of
information, as well as .TIF images that must be inserted, cropped,
and rescaled to fit the worksheet.

Any idea why? and how to correct it?



1 Answer Found

Answer #1    Answered By: Abejundio Garcia     Answered On: Jan 29

Funtions have limitations compared to subroutines. The following
quote comes from

"Can a custom worksheet function written in VBA perform the same
types of actions as a subroutine?

No. Functions called from a worksheet formula have some limitations.
In general, they must be strictly "passive" -- they can't change the
active cell, apply formatting, open workbooks, change the active
sheet, and so on.

Functions can only perform calculations and return a value. An
exception to this rule is the VBA MsgBox function. A custom function
can display a MsgBox whenever it is recalculated. This is very handy
for debugging a custom function."

Possible options are to use a message box if you want to keep it as
a function, or use a subroutine and trigger your subroutine on the
Worksheet_Change event, button press, or another event.

Didn't find what you were looking for? Find more on Inserting text in Textbox in Excel Or get search suggestion and latest updates.