Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    VBA SetFFocus (Word XP)

    In a Text Box in a User Form, I want to ensure that the value entered does not exceed 500. If it does, I display a message and return focus to the text control.

    I am placing my code in AfterUpdate. (Exit doesn't seem to work at all.) The code fires as expected, and my message displays. But I cannot get the focus back to the same text control. It moves to the Cancel button, which is two controls away in the tab order. I tried setting focus to the control that is 2 BEFORE the text box, and that gave me different results. Although the label control associated with the text box has a setfocus method, using that raises a run-time error. I've tried explicitly setting focus to cmdContinue, then DoEvents, the txtBoxRequiredt.SetFocus, and that doesn't behave as expected.,

    If I put this code into the command button that would normally close the dialog, it works exactly as I want it to... the message displays, and focus is set on the text box. But I'd prefer it in the text box before the user has moved away from it. Am I expecting something that can't be accomplished? Is this a bug? Since focus does move, but not to where I want, and not to the next control in the Tab Order, I'm thinking it may be a bug.

    Any ideas?
    Thanks, Richard Barrett

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA SetFFocus (Word XP)

    Use the Before Update event of the text box, and set the Cancel argument to True if the value exceeds 500:

    Private Sub txtBoxRequired_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Val(Me.txtBoxRequired) > 500 Then
    MsgBox "Value may not exceed 500.", vbExclamation
    Cancel = True
    End If
    End Sub

    Setting Cancel = True will prevent the focus from leaving the text box.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: VBA SetFFocus (Word XP)

    Thank you very much. I didn't know about Cancel = True. Why use BeforeUpdate in this situation (other than it works)? In what circumstances would AfterUpdate and Exit be appropriate?

    Richard

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: VBA SetFFocus (Word XP)

    I've given this a try with pretty good success. This functions as expected when I tab through the dialog. If I've entered 501, and tab out, the message displays, and the focus remains in the text box. However, if I mouse click in another control, I get mixed results. If I click a command button or a check box, it's good. If I click an option button or another text box, the message displays twice, requiring two jabs at OK. This latter situation (option button and text box) is not consistent. Sometimes, the msgbox appears twice.

    I've stepped through the code, and the BeforeUpdate sub actually runs twice.

    Any thoughts on this?
    Richard

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA SetFFocus (Word XP)

    Before Update is meant to give the application a chance to check the input, and cancel the update if it doesn't meet all requirements.
    After Update is meant to give the application a chance to modify other controls as a result of the update.
    Both Before Update and After Update occur only if the user has modified the value of the control, they don't occur if the user tabs or clicks out of the control without having changed anything.

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: VBA SetFFocus (Word XP)

    Very clear. Many thanks.
    Regards, Richard

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA SetFFocus (Word XP)

    Do you have other code that changes the value of the text box? In a simple form, I cannot reproduce what you describe. The message box appears only once whether I click on a command button, text box, check box or option box. Only if I click the close button in the upper right corner of the userform do I get the message box twice.

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: VBA SetFFocus (Word XP)

    I've exported the form and tried it on another PC, also with Word XP.... same results. I've attached the form, if you wouldn't mind having a look.

    Thanks, Richard
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA SetFFocus (Word XP)

    I don't understand why, but it occurs because txtNotToExceed is part of fraNotToExceed. If you place the text box and its label directly on the form, the problem doesn't occur. You'll have to change the code that shows/hides the frame to code that shows/hides the text box and label.

  10. #10
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: VBA SetFFocus (Word XP)

    Thanks.... I should have fussed a bit more before "complaining" to you. It's just as easy to scrap the frame and hide the label and text box individually.

    Thanks again,
    Richard

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA SetFFocus (Word XP)

    It's a strange phenomenon. I wonder if anyone else can explain why putting a text box in a frame causes the Before Update event to occur twice in succession.

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA SetFFocus (Word XP)

    In different development environments, certain events "bubble up" from a control through all its containers, like mouse clicks, so a developer can decide where to deal with it. But a textbox update certainly should not do that!

Posting Permissions

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