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!

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Vincent Courtemanche
  • 198
  • 10
  • Have you tried just activating the form (and letting the `UserForm_Activate` handler do its thing) instead of updating it from the outside? – Mathieu Guindon Mar 05 '19 at 20:47
  • I would also try setting the focus on a different object in the UF and then back to the textbox. – pheeper Mar 05 '19 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 – Vincent Courtemanche Mar 06 '19 at 00: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](https://stackoverflow.com/questions/53392937/validation-message-of-text-box-entry-on-modeless-form-interrupts-text-selection/53496763#53496763) – T.M. Mar 06 '19 at 09: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? – Vincent Courtemanche Mar 06 '19 at 17:12

2 Answers2

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

T.M.
  • 9,436
  • 3
  • 33
  • 57
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.

CGorgas
  • 1
  • 1
    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](/help/how-to-answer). – Community Dec 21 '21 at 16:39