Results 1 to 6 of 6

Thread: Userform can't close if refedit in focus
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2012
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Userform can't close if refedit in focus

    Hey guys.


    I've got a userform with a few different refedit objects, and if one of the refedit objects is in focus, or in use, when the user presses the cancel button to close the form the form will close, but Excel becomes stuck in a "select a cell" mode for the previously in focus refedit.


    The form closes properly if the text input object is in focus, just not when a refedit is.


    The cancel button is one I made, that simply clears all field values and then hides the user form. I've tried setting the focus to the text input object before hiding the form, but the same problem occurs. I've also replaced the userform.hide call with a "Unload userform" call, but the problem persists.


    To clarify; if user form.hide is used, the userform window closes and excel is stuck trying to select a range until you force quit the program. If "Unload userform" is used, then the userform window closes, and Excel will crash upon the next mouse click.


    Any ideas?


    Thanks,


    Tim

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,824
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform can't close if refedit in focus

    Hello tim,

    The RefEdit control in VBA is Modal. A modal dialog will lockout input to the UserForm until it has been dismissed by the user. The problems you are experiencing have been known to Microsoft for some time now and it has never been corrected. This is the primary reason most programmers avoid using this control because it can crash Excel. If you need to select cells on a worksheet then you may need to display your UserForm as non-modal. You can set the ShowModal property false when the UserForm is called.
    Code:
    UserForm1.Show Modal:= False
    Sincerely,
    Leith Ross

  3. #3
    New Member
    Join Date
    Dec 2012
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform can't close if refedit in focus

    I was afraid it might have something to do with that…

    Unfortunately, this user form is being built and run on office for Mac 2011. While the VBA in this iteration of office is now 100% identical to the VBA in Windows office, including the existence of the modal properties and methods, modeless forms are not functional, in that even if you set a user form to be modeless you have no access to the underlying worksheet when the user form is active.

    Is there any other workaround that I might try? I'm confused as to why, if the user sets focus to the text input box before closing the form everything works fine, but if I do that programmatically then the problem still persists.

  4. #4
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,824
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform can't close if refedit in focus

    Hello Tim,

    Showing the UserForm as modeless is the easiest workaround. Using a Mac restricts what you can do with VBA. I really don't know of a Mac workaround.

    The RefEdit control by design will set the focus back to its owner window if the user clicks the owner or some control on it. Doing so through code is not allowed. It is just how it was designed.
    Sincerely,
    Leith Ross

  5. #5
    New Member
    Join Date
    Dec 2012
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform can't close if refedit in focus

    I see. This is even worse than when I found out my dream of a floating modeless info window for my budget spreadsheet couldn't be done...Guess I'll code the modeless stuff just for the few window users that'll use this, and put a warning otherwise. Can I fetch the version of excel through Vba so I only warn Mac users?

  6. #6
    New Member
    Join Date
    Nov 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform can't close if refedit in focus

    Set the Tabindex property of the TextBox control to 0, so I have resolved.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •