CODEWITHSUNDEEP
exceleventstextboxvba
Curious
Curious

Reputation: 11

Excel 2011 MAC: TextChange or Change event for a worksheet Textbox

This is is Excel 2011, so no ActiveX controls. I have added a textbox to a worksheet (not a userform) and I need to be able to detect when the user enters text into the textbox and also be able to know what that text is as the user is typing it.

I have not been able to attach any events to this textbox. 

Sub textmove()

    Sheets("Sheet1").Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, _
    1530.25, 520#).Name = "TextBox2"




    Sheets("Sheet1").Shapes("Textbox2").TextFrame.Characters.Text = "This is my textbox"

    Sheets("Sheet1").Shapes("Textbox2").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 45
        .StrikeThrough = False
        .Superscript = False
        .Subscript = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
  End Sub

But how do I attach an event to it? I tried this in the worksheet code and I also tried it in the workbook code page:

Private Sub Textbox2_change()
    MsgBox ("event fired.")
End Sub

but that did not work.

Also, what events are there for a worksheet textbox?

I have not been able to find anything online.

What am I missing?!

Thanks.

Upvotes: 1

Views: 589

Answers (1)

paul bica
paul bica

Reputation: 10705

This made me curious as well. It looks like if you have to use a TextFrame you're quite limited when triggering events. One (ugly) way to simulate what you're trying to do:

Sheet1 module:

Option Explicit

Private Sub Worksheet_Activate()
    Test
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Test
End Sub

A new User module:

Option Explicit

Public shp As Shape
Private ws As Worksheet

Public Sub Test()

    Const SPACE_CHAR As String = " "

    Dim sz As Long

    Set ws = Sheet1
    If ws.Shapes.Count > 0 Then Set shp = ws.Shapes(1) Else makeTextFrame

    If Not shp Is Nothing Then
        With shp.TextFrame2.TextRange
            With .Characters
                sz = Len(.Text)
                If sz > 0 Then
                    If InStr(1, .Text, "x") > 0 Then
                        .Text = Replace(.Text, "x", vbNullString)
                    End If
                End If
                If Right(.Text, 1) = SPACE_CHAR Then .Text = Left(.Text, sz - 1)
                .Text = .Text & SPACE_CHAR
                sz = Len(.Text)
            End With
            If sz > 1 Then .Characters(sz).Select Else .Characters.Select
        End With
    End If
End Sub

Private Sub makeTextFrame()
    Set shp = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 250, 150)
    shp.OnAction = "'test'"
End Sub

A side effect is that a user will not be able to type anywhere else in the range of Sheet1 (can be an advantage if it's what you intend)

Upvotes: 0

Related Questions