Userform can't close if refedit in focus

[twilsonco]

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

 

[Leith Ross]

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.

UserForm1.Show Modal:= False

 

[twilsonco]

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.

 

[Leith Ross]

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.

 

[twilsonco]

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?

 

[walteraul]

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