none
Form textbox data entry causes worksheet form button not to work RRS feed

  • Question

  • I have a data entry worksheet (excel2010) with multiple form text boxes created at runtime (using below code), several buttons, and several combo boxes (none are activex).

    Dim tBox As TextBox

    Dim tShp As Shape

           Set tShp = Sheets(sht).Shapes.AddTextbox(msoTextOrientationHorizontal, .Left, .Top, .Width, .Height)
           With tShp
                    .DrawingObject.Font.OutlineFont = False
                    .DrawingObject.Font.Shadow = msoFalse
                    .DrawingObject.Font.Size = 10
                    .TextFrame2.TextRange.Characters.Font.Line.ForeColor.RGB = RGB(0, 0, 0)
                    .TextFrame2.TextRange.Characters.Font.Line.Visible = False
                    .TextFrame2.TextRange.Characters.Font.Line.Transparency = 0
                    .TextFrame2.TextRange.Characters.Font.Bold = msoFalse
                    .TextFrame2.WordWrap = msoFalse
               End With           
               Set tBox = tShp.OLEFormat.Object
               With tBox
                   .Locked = True
                   .LockedText = False
                   .PrintObject = False
                   .name = "Textbox" ' name passed to this procedure in the real code
                End With

           End With

    Also on the worksheet are several form (not activex) control Buttons that fire macros to process the data entered when the button is clicked.  The problem is that when text is entered in any of the textboxes, then moving the mouse to the command button, clicking there does not work (click is ignored).  If you move to a second button it may work or it may not work. Sometimes you need to click on something like a combo box (also on the data entry screen)  first, then the command button clicks will work.  It seems that after entering something into the textbox, it keeps the focus even if you click outside the textbox.  Also note the text form controls are on a protected worksheet (top half is for the textbox data entry, bottom half is where results are displayed).  Does anyone have any ideas on a work around for this problem?

    Thanks is advance

    Allan



    • Edited by AllanCr Thursday, December 22, 2016 7:19 PM
    Thursday, December 22, 2016 6:44 PM

All replies

  • Hi AllanCr,

    I can hardly reproduce your situation.
    Could you share your file via cloud storage, such as Dropbox, OneDrive?
    (with removing critical, private data)

    Regards,
    Ashidacchi
    Friday, December 23, 2016 2:35 AM
  • Hi Ashidacchi I created a small xlsm, it should reproduce the problem. Clicking into a textbox, entering some text and then clicking one of the buttons doesn't always work. The problem is intermittent, it doesn't always fail, sometimes you need to go back to the textbox and try adding more text then try clicking (back and forth). The only alternative I can see is to use an ActiveX textbox, however since this data entry worksheet is created at runtime, adding ActiveX controls at runtime causes a vb recompile, clearing all public variables so that is also a problem. https://1drv.ms/f/s!AmdwdGwJOExfpRESkTA1eTtESy8v Regards Allan
    Friday, December 23, 2016 12:04 PM
  • Hi AllanCr,

    Thanks.  I've download your sample xlsm.
    Please give me time to try and think about it.

    Regards,
    Ashidacchi
    Friday, December 23, 2016 1:04 PM
  • Hi Ashidacchi

    Just FYI, interesting, I replaced the form textboxes and tried ActiveX textboxes, and the same issue appears when using Activex controls.  This surprised me as I thought this would be related to only one type of textbox control.  Now I'm wondering why it happens in both cases and is there any alternative?

    Regards
    Allan

    Monday, December 26, 2016 8:48 PM
  • Hi Allan,

    I've checked your file in the below environments, and it worked fine.
      a) Windows 7 (64-bit), Excel 2010 (32-bit)
      b) Windows 7 (64-bit), Excel 2016 (32-bit)
      c) Windows 10(64-bit), Excel 2016 (32-bit)

    When caret is in a textbox, i.e. when a textbox is active, 
    button [OK] and [Clear] cannot be clicked. (It's very natural.)

    As for me, one question is still left. 
    While Design-Mode is ON and when I click [OK] or [Cancel], they work as a button, instead of opening code editor.

    Regards,
    Ashidacchi
    • Edited by Ashidacchi Monday, December 26, 2016 11:19 PM
    Monday, December 26, 2016 11:16 PM
  • Hi,

    I could reproduce your issue with Sheet1. The issue occurs intermittently.

    However, in Sheet2, I create two buttons(add two shapes) and assign macros to them. It works fine. Add two shapes as buttons in Sheet1, it also works fine.

    What are the buttons in Sheet1? How do you add them?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 27, 2016 7:04 AM
    Moderator
  • Hi Allan,

    As Celeste Li asked, I also want to know how you created [OK] [Clear] buttons on Sheet1.

    I cannot identify what they are, i.e. cannot show property of these objects.
    I suspect they might have been created via other than Excel.

    Regards,
    Ashidacchi
    Tuesday, December 27, 2016 7:21 AM
  • Hi Ashidacchi/Celeste

    First, the buttons where made by going into design mode and then inserting a form control button (and assigning a macro), not an ActiveX button.

    Ashidacchi, you indicated

     When caret is in a textbox, i.e. when a textbox is active, 
     button [OK] and [Clear] cannot be clicked. (It's very natural.)

    I'm not sure I understand your comment, on a normal data entry screen, command buttons or form buttons are always active and can be click regardless of where the caret is located.

    Celeste, I did as you, created the Sheet2 example and I still see the problem.  It is intermittent in nature and does not always happen on every button click attempt.

    Regards

    Allan

    Tuesday, December 27, 2016 1:04 PM
  • Hi,

    In Office2010/2013/2016, I add a textbox(Shape) and a button(Form Control) manually. The Click event fails because the shape is focused. I think this should be built-in behavior. I suggest you add an ActiveX button or use a shape as button to work around.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 28, 2016 5:40 AM
    Moderator
  • Ashidacchi, you indicated

     When caret is in a textbox, i.e. when a textbox is active, 
     button [OK] and [Clear] cannot be clicked. (It's very natural.)

    I'm not sure I understand your comment, on a normal data entry screen, command buttons or form buttons are always active and can be click regardless of where the caret is located.



    Hi Allan,

    Yes, you are right.
    Buttons can be clicked regardless of where the caret is located.
    Sorry for my wrong comment.

    I agree with Celeste.  I think it's better to use ActiveX controls for creating Text Box and Command Button.

    Regards,
    Ashidacchi

    Wednesday, December 28, 2016 7:46 AM
  • Hi Ashidacchi

    I was hesitant to start using ActiveX control for 2 reasons.

    First, there seems to be security issues when using ActiveX controls on worksheets.  In the past, I've added ActiveX controls at design time and sent out xlsm to users.  Some users then complain they can't run the worksheets because of some security errors (sorry, I forgot the actual error message).  I've stopped using any ActiveX controls on on worksheets (they still appear on userForms which is fine). 

    Second, the data entry screens I'm using are created interactively at runtime (depending upon user selections).  When adding ActiveX controls at runtime, the vbproject is recompiled causing any public data stored on the heap to be lost, basically the xlsm then needs to be restarted.  I store variables on the heap so I would need change the way these items are accessed (a big change as the code size for this xlsm is very large).

    Regards
    Allan

    Wednesday, December 28, 2016 11:51 AM
  • Hi Ashidacchi

    Another interesting behavior I just noticed.  The behavior is not intermittent but rather dependent on where you click.  In my testing, if I enter text into one of the textbox fields and then move the cursor to one of the buttons, placing the cursor directly over the button text (right over Ok or Clear), then click fails every time.  Placing the cursor over the empty space on the button, the click appears to work correctly every time.  Perhaps this is because of the way the control is built, the text does not intercept the click, I'm not sure.  The missing click event also happens with a forms label, if the form button is a shape with a label applied on top, then maybe that explains the problem. Now I have to figure out a way to workaround this limitation, however I also have some very small buttons on my data entry screen where there is very little free space (button is mostly text) so I'm not sure how to get around this issue?

    Regards
    Allan


    • Edited by AllanCr Wednesday, December 28, 2016 1:18 PM
    Wednesday, December 28, 2016 1:05 PM
  • Hi Allan,

    Thank you for your explaining details.

    I'd like to know the exact, or more realistic sheet image where Text Boxes and Buttons are on.
    Moreover, I'd like to know how many objects or shapes are there, and their location (layout on a sheet).

    Could you share an image (screenshot) of your sheet? 

    # I'm poor at English, and hard to explain exactly what I want. I hope you will imagine my intention.

    Regards,
    Ashidacchi
    Wednesday, December 28, 2016 1:27 PM
  • Wednesday, December 28, 2016 1:45 PM
  • Hi Ashidacchi

    The screens are built depending if the user clicks the Alert or EG shape.  Other shapes determine time range (1 Hour, 3 Hours etc).  As well as the Search, Clear and Set Default form buttons, there are other form buttons with a single character (like the = ones) used to pop up a menu to allow search type selection (equal, not equal, like, not like).  

    The other shapes also have the same issue as the form buttons, trying to click directly over the text fails, clicking on a empty area works.

    Regards
    Allan

    Wednesday, December 28, 2016 1:51 PM
  • Hi Allan,

    I'll think about them tomorrow (it's around 23 o'clock in Japan). Thank you.

    Regards,
    Ashidacchi
    Wednesday, December 28, 2016 2:02 PM
  • Hi Allan,

    I've made a sample file and shared it.
    Excel2010_Allan_Ashidacchi.xlsm

    This sample is for searching files in a specific folder and display them in sheet.
    Users can select the way of searching, by Filename or by extention.
    (I have not written code for searching)

    I suppose it's better to use Cell for input text, instead of TextBox.

    I hope it will be helpful.

    Regards,
    Ashidacchi

    • Edited by Ashidacchi Thursday, December 29, 2016 4:38 AM
    Thursday, December 29, 2016 4:19 AM
  • Hi Ashidacchi

    I'll look at the example file when I get home tonight, dropbox is blocked at work.  

    I originally used the excel cells as input but found it a bit limiting, pasting does not work on merged cells and excel has a habit of pasting an image instead of text into a cell when copied from another cell which was causing some issues.  I see now that switching to a form textbox was not the way to go.  I think my only option would be to convert to ActiveX controls but as I mentioned there were some issues (Microsoft released a update early last year that caused a lot of users to experience problems, I'm not sure if this has been addressed by subsequent updates or not).

    Regards
    Allan 

    Thursday, December 29, 2016 11:51 AM
  • Hi Allan,

    How is your issue?
    Have you found some solutions?

    Regards,
    Ashidacchi
    Wednesday, January 4, 2017 4:55 AM
  • Hi Ashidacchi

    Sorry for not responding earlier, has been very busy here.  Anyway, what I have done is removed all the original form controls and replaced with corresponding ActiveX controls.  This has resolved the click problems I was experiencing, I'm hoping the ActiveX controls won't trigger any security problems like we had earlier. 

    Thanks for your efforts in trying to resolve the form textbox control issues.

    Regards
    Allan

    Wednesday, January 4, 2017 7:56 PM
  • Hi Allan,

    I'm very happy to hear you could resolve your issues, and hoping no security problems will occur with ActiveX.

    Regards,
    Ashidacchi
    Wednesday, January 4, 2017 10:34 PM