none
Excel Worksheet Textbox Bugs - Workarounds? RRS feed

  • Question

  • I need a textbox on a worksheet. Excel only provides two options for this: (1) an ActiveX textbox control and (2) a shapes textbox. Unfortunately both of these textbox options appear to have serious shortcomings (bugs?).

    I want to use "second windows" for popup user interfaces (containing structured tables for selection options), but an ActiveX textbox will not work when displayed in a second window that shows a workbook worksheet. You just can't select it - period. Its just dead (bug?).

    So I reverted to utilizing Shape textboxes. While shape textboxes allegedly have a click event, this is not quite true. The shape click event seems to belong to the worksheet and not the shape itself. If you have selected a worksheet object (e.g. a cell) and click on a shape, then the click event fires. BUT, if you have already selected a shape and then select a second shape the second shape's click event does not fire (bug?).

    Does anyone know of any way to work around these bugs? (To me these are "bugs" because neither textbox type 'works as advertised')


    phillfri

    Tuesday, March 26, 2019 4:43 AM

Answers

  • Found a workaround for Shape textboxes on a worksheet. I'll show code for others. Bit of a kludge, but it works. (I use line numbers for error trapping and I've left some miscellaneous lines and comments out). Requirements.

    • Worksheet protect (without UserinterfaceOnly!)
    • Shape Textbox text editing allowed.
    • Set to Do not move or size with cells.
    • Procedure should be called from each shape on worksheet.
    Public Sub shpTextBox(Optional sSelName As String)
          Static sPrevShapeName As String
          Dim UserSelection As Variant
          Dim ActiveShape As Shape
    
          'Get the name of the calling control
    40    If Len(sSelName) = 0 Then
    50        sSelName = ActiveSheet.Shapes(Application.Caller).Name
    60    End If
    
          'Select the calling shape
    70    ActiveSheet.Shapes(sSelName).TextFrame2.TextRange.Select
    80    SendKeys "{END}"
    
          'Save the calling shape's name
    90    If Len(sPrevShapeName) = 0 Then sPrevShapeName = sSelName
    
          'Loop until user exits shapes
    100   Do
    110       Set UserSelection = ActiveWindow.Selection
    120       On Error GoTo ExitCode
    130       Set ActiveShape = ActiveSheet.Shapes(UserSelection.Name)
    140       On Error Resume Next
              
    150       Select Case ActiveShape.Type
                  Case msoTextBox
    160               If ActiveShape.Name <> sPrevShapeName Then
                          'Run code for prior active shape processing here (Exit Event)
    170                   '>>>>>>>>>>>>>>>>>>>>
                          'Capture the active shape name and save it
    180                   sPrevShapeName = ActiveShape.Name
                          'Run Code for active shape processing here (Enter Event)
                          '>>>>>>>>>>>>>>>>>>>>
                          'Re-select the Activeshape
    190                   ActiveShape.TextFrame2.TextRange.Select
    200                   SendKeys "{END}"
    210               End If
    220           Case msoAutoShape
    230               Select Case ActiveShape.Name
                          Case "btnSave"
    240                       btnSave
    250                       ActiveWindow.Close
    260                       GoTo ExitCode
    270                   Case "btnCancel"
    280                       MsgBox "btnCancel"
    290               End Select
    300       End Select
    
    310       DoEvents
    320   Loop
    
    ExitCode:
    330   On Error GoTo 0
    340   Exit Sub


    phillfri

    • Marked as answer by phillfri Tuesday, March 26, 2019 7:35 PM
    Tuesday, March 26, 2019 7:30 PM

All replies

  • You could create a userform in the Visual Basic Editor and place a text box on the userform...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, March 26, 2019 9:29 AM
  • The problem is with worksheet ActiveX controls is they only work in the worksheet window in which they were created, at least while multiple windows exist. Textboxes won't update in real time over multiple windows.

    One workaround - just after creating or activating a new or different window, retain coordinates of other existing windows, close them, recreate them in previous respective positions, and re-activate the 'new' window. (Could also rename window captions to suit.) This way all aX controls should now work in the required window. Similar idea and perhaps simpler, when creating or activating a new or different window, place the new window where the original window was and move the original window to where the new window is intended to be and activate it (assumes aX controls are known to work in this window). 

    Forms controls (normal shapes) do not support events except the OnAction property which can call a macro (in a normal module). This will work in the expected way over multiple worksheets, however with a textbox it would be awkward to enable editing. 

    Tuesday, March 26, 2019 10:10 AM
    Moderator
  • Found a workaround for Shape textboxes on a worksheet. I'll show code for others. Bit of a kludge, but it works. (I use line numbers for error trapping and I've left some miscellaneous lines and comments out). Requirements.

    • Worksheet protect (without UserinterfaceOnly!)
    • Shape Textbox text editing allowed.
    • Set to Do not move or size with cells.
    • Procedure should be called from each shape on worksheet.
    Public Sub shpTextBox(Optional sSelName As String)
          Static sPrevShapeName As String
          Dim UserSelection As Variant
          Dim ActiveShape As Shape
    
          'Get the name of the calling control
    40    If Len(sSelName) = 0 Then
    50        sSelName = ActiveSheet.Shapes(Application.Caller).Name
    60    End If
    
          'Select the calling shape
    70    ActiveSheet.Shapes(sSelName).TextFrame2.TextRange.Select
    80    SendKeys "{END}"
    
          'Save the calling shape's name
    90    If Len(sPrevShapeName) = 0 Then sPrevShapeName = sSelName
    
          'Loop until user exits shapes
    100   Do
    110       Set UserSelection = ActiveWindow.Selection
    120       On Error GoTo ExitCode
    130       Set ActiveShape = ActiveSheet.Shapes(UserSelection.Name)
    140       On Error Resume Next
              
    150       Select Case ActiveShape.Type
                  Case msoTextBox
    160               If ActiveShape.Name <> sPrevShapeName Then
                          'Run code for prior active shape processing here (Exit Event)
    170                   '>>>>>>>>>>>>>>>>>>>>
                          'Capture the active shape name and save it
    180                   sPrevShapeName = ActiveShape.Name
                          'Run Code for active shape processing here (Enter Event)
                          '>>>>>>>>>>>>>>>>>>>>
                          'Re-select the Activeshape
    190                   ActiveShape.TextFrame2.TextRange.Select
    200                   SendKeys "{END}"
    210               End If
    220           Case msoAutoShape
    230               Select Case ActiveShape.Name
                          Case "btnSave"
    240                       btnSave
    250                       ActiveWindow.Close
    260                       GoTo ExitCode
    270                   Case "btnCancel"
    280                       MsgBox "btnCancel"
    290               End Select
    300       End Select
    
    310       DoEvents
    320   Loop
    
    ExitCode:
    330   On Error GoTo 0
    340   Exit Sub


    phillfri

    • Marked as answer by phillfri Tuesday, March 26, 2019 7:35 PM
    Tuesday, March 26, 2019 7:30 PM