1

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!

Improve this question
5
  • Have you tried just activating the form (and letting the UserForm_Activate handler do its thing) instead of updating it from the outside? Commented Mar 5, 2019 at 20:47
  • I would also try setting the focus on a different object in the UF and then back to the textbox. Commented Mar 5, 2019 at 20:54
  • I haven't found a way to make the UserForm_Activate event fire after loading. I may not have mentionned the UF is displayed modally, so I can't use .Show for example Commented Mar 6, 2019 at 0:08
  • What about 1) switching and reswitching the .Enabled property or alternativly 2) explicitly losing focus via any other control accepting focus (including also commandbuttons or frames) and reset focus again :-) c.f. Validation message of textbox entry on modeless form interrupts text selection Commented Mar 6, 2019 at 9:02
  • 1
    that works! actually, cycling either the .Enabled or .Visible properties, or moving the .SetFocus away then back on the control all seem to work equally well :) Do you want to post this as the answer? Commented Mar 6, 2019 at 17:12

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

As a quick aside, I was grappling with all the same symptoms, but none of the prescribed answers worked. It was only occurring with one text box in particular. After messing with it for 2 hours and reading all the above (and the links) and trying the various suggestions, I decided to delete the offending text box and recreate it. When I deleted the text box in question, I realized that I had somehow contrived to have another text box in the same position (copy/past hiccup, I think). When I tabbed through my fields, it was going to the generically-named "TextBox2" behind the intended target, but when I clicked on the box, it would set focus correctly. Needless to say, deleting the spurious text box cleared up my issues. I'm posting this "answer" in case someone is encountering a similar issue.

Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.