Consulting

Results 1 to 13 of 13

Thread: Stay in TextBox - UserForm

  1. #1
    VBAX Regular
    Joined
    Aug 2005
    Posts
    79
    Location

    Stay in TextBox - UserForm

    I have a UserForm and for a TextBox I have the following code associated with it:

    [VBA]

    If IsNumeric(TextBox130.Value) Then

    TextBox130.Value = Round(TextBox130.Value, 0)
    JobArea(0).SqFt = TextBox130.Value
    Call UpdateSqFtTotal

    Else

    MsgBox ("Please input a Number!"), vbInformation, "Data Input"
    TextBox130.Text = ""

    End If

    [/VBA]

    I want the cursor to return to the TextBox so that the user can start entering the correct data.

    I have this code in a AfterUpdate(). I also tried putting it in an Exit() with Cancel set to True but I am not getting what I want.

    Suggestions or questions.
    Thanks

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Try this:
    [vba]
    Me.TextBox130.SetFocus
    [/vba]

  3. #3
    VBAX Regular
    Joined
    Aug 2005
    Posts
    79
    Location
    That does not work. I am still not staying in the textbox I was before going to the msgbox. Is there a way to use tabindex to move around so that I could tell the cursor to be in the textbox indicated by the tabindex?

    Thanks

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Can you post an attachment?

  5. #5
    VBAX Regular
    Joined
    Aug 2005
    Posts
    79
    Location
    DRJ

    The code I sent you is what I am using. What kind of an attachement do you want?

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Nils,
    I see the problem. Basically you're trying to return to a textbox within a macro triggered by Exit or AfterUpdate. I don't have a workaround at the moment, but I'll give it some thought.
    Regards
    Malcolm
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #7
    VBAX Regular
    Joined
    Aug 2005
    Posts
    79
    Location
    Malcolm,

    Here is a new copy of my code. The problem I have discovered appears to be that the cursor is not present. I believe that the focus is still at the TextBox130 after the MsgBox appears and you hit OK but there is no cursor indication. If you type, nothing appears in any box. If you click anyplace else, you get the MsgBox for the TextBox130.

    I placed a breakpoint at the If statement and the MsgBox and after the MsgBox nothing happens when you type. The cursor is not blinking any place on the UserForm. So that is why I feel I have lost focus.

    I am using Excel 2000. I did have the TexBox130 = "" after the MsgBox and I also had TextBox130.SetFocus but it did not help.

    If you remove the MsgBox code, the code works fine. If you put in a character, it is erased and the cursor is at the TextBox ready for input. The MsgBox appears to be causing the problem.

    Any suggestions?

    Private Sub TextBox130_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim MyPrompt As String
    MyPrompt = "Please input a Number!"
     
    If IsNumeric(TextBox130.Value) Then
     
    TextBox130.Value = Round(TextBox130.Value, 0)
    JobArea(0).SqFt = TextBox130.Value
    Call UpdateSqFtTotal
     
    Else
    Cancel = True
    TextBox130.Text = ""
     
    MsgBox MyPrompt, vbInformation, "Pro Paver Installer Data Input"
    End If
    End Sub

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Nils,

    I put this userform together with your code (slightly modified for output) and it works for me with or without the message. I've tried it in 2003 and 2000. Do you still get the same problem?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    The trouble with trying to set focus like this is that it happens but then gets overridden by later actions before control is returned to the user. Input validation should go in the BeforeUpdate event and Cancel = True set if it fails - this suppresses all the queued actions and returns immediately to the user with the focus still in the textbox. As a general rule, non-validation code using the input should go after update so you really want to split your code into two, something like ..

     Sub Text130_BeforeUpdate(Cancel as Boolean)
        If IsNumeric(etc ... ... ... Then
            Cancel = True
        End If
    End Sub
    
    Sub Text130_AfterUpdate()
        :
    :
        Call YourRoutine
    End Sub
    Sorry I pressed the wrong button and when I edit the post I can't see the rest of the thread so can't cut and paste all the relevant bits of code - I hope you can follow it.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  10. #10
    VBAX Regular
    Joined
    Aug 2005
    Posts
    79
    Location
    I like you suggestion and I will give it a try later.

    I did post my problem on another form and they tested a sample of the code that did not work on my machine with Excel 2000 but it worked on their 2003 and 2000. Could it be that my Excel is configured differently or some option set differently?

    I am frustrated with the problem. But I did read about BeforeUpdate and I would think that it should also work. It will be interesting to see if it does. I will post my code after I have time to test it.

    Thanks for the suggestion.

  11. #11
    VBAX Regular
    Joined
    Aug 2005
    Posts
    79
    Location
    Tony, is this what you suggested?

    [VBA]
    Private Sub TextBox130_AfterUpdate()

    If IsNumeric(TextBox130.Value) Then

    TextBox130.Value = Round(TextBox130.Value, 0)
    JobArea(0).SqFt = TextBox130.Value
    Call UpdateSqFtTotal

    End If
    End Sub

    Private Sub TextBox130_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

    Dim MyPrompt As String
    MyPrompt = "Please input a Number!"

    If Not IsNumeric(TextBox130.Value) Then

    Cancel = True
    TextBox130.Text = ""
    MsgBox MyPrompt, vbInformation, "Pro Paver Installer Data Input"

    End If

    End Sub
    [/VBA]

    I get the same results with this...It doesn't work. I believe that this should have worked after reading about the way BeforeUpdate works.

    I also believe now that there is something wrong with my Excel.

    Open to suggestions!

  12. #12
    VBAX Newbie
    Joined
    Mar 2016
    Posts
    1
    Location
    Try this

    Private Sub TextBox130_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    With TextBox130
    If KeyCode = 9 Or KeyCode = 13 Then

    'It will stay in TextBox130 no matter you press Enter or TAB
    KeyCode = 0

    'These 2 statements will highlight the text. It's helpful for correcting invalid value
    .SelStart = 0
    .SelLength = Len(.Text)
    End if
    End With

    End Sub

  13. #13
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    1
    Location
    I had a similar problem which was fixed with the following code:

    Private Sub TbNewDolphinName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    'Check if name in use?
    Dim X
    Set ExistingName = Worksheets("Dolphins_Locations").Range("ExistingNames").Find(TbNewDolphinNa me.Value)
    If Not ExistingName Is Nothing Then


    X = MsgBox("The name '" + TbNewDolphinName.Value + "'" + " already exists choose another one.", vbOKOnly, "Enter another name")

    TbNewDolphinName = TbNewDolphinName + " ?"
    Cancel = True

    End If
    End Sub

Posting Permissions

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