excelvbauserformsetfocus

VBA UserForm: Unexpected behaviour after using SetFocus on a TextBox

I have a UserForm that displays the text content of various cells within the active worksheet and has a TextBox to allow users to enter new information.

The UF updates automatically whenever the user selects a new cell/range through the Workbook_SheetSelectionChange event.

At the end of the UF Update procedure (a public sub within the UF code module) I use TextBoxName.SetFocus (along with .SelStart = .TextLenght) to set the focus on the text box, ready for the user to start typing.

Now this update procedure also runs when the UF activates through the UserForm_Activate event.

Here's my problem

When the UF activates, the focus is successfully set on the TextBox , with the cursor visible at the end of the text, and whatever I type is entered in the TextBox as expected.

BUT, if I click on a new cell, which runs the same update procedure except from the Workbook_SheetSelectionChange event, something weird happens. The focus is technically on the TextBox, but the cursor isn't visible. Spacebar, Enter and Backspace all work as expected, however the letters and number keys don't. I.e. I can delete text or tap enter to add new lines, but if I tap any other number/letter key nothing happens, until I use the mouse to click on the TextBox again.

I've tried moving the procedure out of the UF module, using .SetFocus at different places, including after the Workbook_SheetSelectionChange calls the update procedure, but nothing works.

What else can I try?

Thanks in advance!

Solution

  • Making textbox cooperative again

    Some (window) processes can hinder the .SetFocus to show full effect, such as a MsgBox window displayed during textbox validation or even a complete update procedure raised by the Workbook_SelectionChange event while clicking on a worksheet cell as in your case. Though you explicitly set focus or rather because the control internally didn't lose (complete) focus, so the control's focus won't/can't be reset as the Userform "has" it already.

    To overcome the unsatisfactory situation you can

    1. switch and reswitch the .Enabled property or alternatively
    2. explicitly lose focus via any other control accepting focus (including also commandbuttons or frames) and reset focus again.

    Related link

    To get more insight I recommend to see the explanations given at Validation message of textbox entry on modeless userform interrupts text selection

    Acknowledgements CommonSense