Adding a textbox programmatically

[tmanger]

Does anybody have any ideas on how to add a textbox programmatically to a worksheet, and then alter the properties.

If I try recording a macro for this purpose, I am only able to add the textbox. I can't seem to turn on some of the properties needed: eg. multiline, enterkeybehavior, etc.

Thanks in advance.

Tom.

 

[Joe Was]

To use this example, copy this sample code to the Declarations portion of a form. Make sure that the form contains:

A TextBox named TextBox1.


Four ToggleButton controls named ToggleButton1 through ToggleButton4.
To see the entire text placed in the TextBox, set MultiLine and WordWrap to True by clicking the ToggleButton controls.

When MultiLine is True, you can enter new lines of text by pressing SHIFT+ENTER.

ScrollBars appears when you manually change the content of the TextBox.

Private Sub UserForm_Initialize()
'Initialize TextBox properties and toggle buttons

TextBox1.Text = "Type your text here. "
& "Enter SHIFT+ENTER to move to a new line."

TextBox1.AutoSize = False
ToggleButton1.Caption = "AutoSize Off"
ToggleButton1.Value = False
ToggleButton1.AutoSize = True

TextBox1.WordWrap = False
ToggleButton2.Caption = "WordWrap Off"
ToggleButton2.Value = False
ToggleButton2.AutoSize = True

TextBox1.ScrollBars = 0
ToggleButton3.Caption = "ScrollBars Off"
ToggleButton3.Value = False
ToggleButton3.AutoSize = True

TextBox1.MultiLine = False
ToggleButton4.Caption = "Single Line"
ToggleButton4.Value = False
ToggleButton4.AutoSize = True
End Sub

Private Sub ToggleButton1_Click()
'Set AutoSize property and associated ToggleButton

If ToggleButton1.Value = True Then
TextBox1.AutoSize = True
ToggleButton1.Caption = "AutoSize On"
Else
TextBox1.AutoSize = False
ToggleButton1.Caption = "AutoSize Off"
End If
End Sub
Private Sub ToggleButton2_Click()
'Set WordWrap property and associated ToggleButton

If ToggleButton2.Value = True Then
TextBox1.WordWrap = True
ToggleButton2.Caption = "WordWrap On"
Else
TextBox1.WordWrap = False
ToggleButton2.Caption = "WordWrap Off"
End If
End Sub
Private Sub ToggleButton3_Click()
'Set ScrollBars property and associated ToggleButton

If ToggleButton3.Value = True Then
TextBox1.ScrollBars = 3
ToggleButton3.Caption = "ScrollBars On"
Else
TextBox1.ScrollBars = 0
ToggleButton3.Caption = "ScrollBars Off"
End If
End Sub
Private Sub ToggleButton4_Click()
'Set MultiLine property and associated ToggleButton

If ToggleButton4.Value = True Then
TextBox1.MultiLine = True
ToggleButton4.Caption = "Multiple Lines"
Else
TextBox1.MultiLine = False
ToggleButton4.Caption = "Single Line"
End If
End Sub

 

[tmanger]

Thanks. I have seen that example in the help for VBE. I should be more specific with my intial inquiry: I am trying to add a textbox to a worksheet programmatically and control its parameters so that the user can add notes on the worksheet before saving.

Perhaps it's not possible to do what I want except on a UserForm, or maybe someone has an idea regarding another solution?

All suggestions appreciated and welcome.

Tom.

 

[Mudface]

Using the macro recorder to do this generated the following (cleaned-up) code: -

ActiveSheet.OLEObjects.Add ClassType:="Forms.TextBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=99.75, Top:=31.5, Width:=105, Height:= _
        48.75

Take a look at the Add method for the OleObjects collection for info on the parameters above (and also an example of adding a command button to a worksheet). HTH.

 

[tmanger]

Appreciate the input. I had tried that initially as well using the macro recorder. I also played around with using the AddShapes command:

Sub addbox()
Set myDocument = Worksheets(1)


myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50) _
.TextFrame.Characters.Text = "Enter your notes or instructions here."

ActiveSheet.Shapes("Text Box 1").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 1.5
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 12
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
With Selection
.Locked = True
.LockedText = False
End With
With Selection
.Placement = xlFreeFloating
.PrintObject = True
End With

End Sub

This gets me where I need to go, except that I can't figure out a way to generically name the TextBox. The way this macro was recorded, it will only work the first time, and give me an error that it can't find the object specified when it is run again.

I am sure the answer is simple, but it continues to confound me.

Thanks for any insight.

Tom.

 

[Andrew Poulsom]

Set an object variable for the Textbox when you add it, eg

Set MyBox = myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50) _ 
.TextFrame.Characters.Text = "Enter your notes or instructions here."

Then you can refer to MyBox to set other properties (or delete it when done).

 

[tmanger]

I tried that as well. I hate to be difficult or thick-headed. When I set that parameter, I get a "type mismatch" run-time error.

What am I doing wrong!?

Frustrated,

Tom.

 

[Mudface]

To expand further on my and Andrew's answers, the following will add a textbox in the top left of the sheet in a similar style to the one you created. You can refer to it as in the second procedure below, where it's text is inserted into cell A1 and it is then deleted: -

Public Sub AddTextbox()
Dim myTextBox As OLEObject

Set myTextBox = Worksheets(1).OLEObjects.Add(ClassType:="Forms.TextBox.1")
With myTextBox
    .Left = 1
    .Top = 1
    .Name = "myTextBox"
    .Width = Range("A1:D1").Width
    .Height = Range("A1:A4").Height
    With .Object
    .Text = "Enter your notes or instructions here."
    .BorderStyle = fmBorderStyleSingle
    .BorderColor = RGB(0, 0, 255)
    End With
End With

End Sub

Public Sub DeleteTextBox()

Range("A1") = Worksheets(1).OLEObjects("myTextBox").Object.Text
Worksheets(1).OLEObjects("myTextBox").Delete

End Sub

 

[tmanger]

Perfect!

I'll work with it.

Thanks again.

Tom.