Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    seting focus (any)

    I have a userform with a texdtbox in it. After the user enters a number it check if the number is valid and if not displays an error message and clears the text box. My question is how do you get it so that the cursor is back in that textbox waiting for the user to enter another value. right now it goes to the next textbox.
    thanks-

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: seting focus (any)

    Me.Textbox1.SetFocus

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: seting focus (any)

    unfortunatly this did not seem to work. Maybe because it was already in 'focus' or something I am not sure. Any other ideas?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: seting focus (any)

    What version of Excel did it fail to work with ?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: seting focus (any)

    What version of Excel, and please define "did not seem to work." What did it do? Any error message?
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: seting focus (any)

    If you put the validation code in the change event, you don't have to worry about resetting focus:

    Here's a start:
    <pre>Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    ' Edit out the obvious
    Select Case KeyAscii
    Case 45, 46, 48 To 57 'Minus, Period, 0 - 9
    Case Else
    Beep
    KeyAscii = 0
    End Select
    If Not IsNumeric(TextBox1) And Len(TextBox1) > 0 Then
    ' If all the characters are correct, ensure the final
    ' number is OK
    Beep
    KeyAscii = 0
    End If
    End Sub
    </pre>


    That's a start anyway- it doesn't allow numbers after a minus sign, and it allows 2 decimal points. Both of which should be solvable.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: seting focus (any)

    Okay, maybe I should expalin in more detail, sorry.
    I have a from with many text boxes for inputing data. some of the text boxes CALL a subroutine that checks to insure the data is numeric so I made a generic subroutine for this. Next, 4 of the boxes checks that the data is within a certain range all of them being the same so I again made a subroutine which is called to figure it out and display an error message if the value os outside of the range. If the value IS outside than the textbox is cleared but then it automatically sets focus on the next text box in the tab order. What I want it to do is clear the text box and stay in that textbox waiting for a new input. I am programming it in excel 2000.

    Thanks again for help and sorry about the lack of explanantion earlier

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: seting focus (any)

    Are you passing a parameter to the subroutine to tell it which textbox to clear? If not, then how are you clearing it. If you have code that clears the textbox, then show it to us. A slight modification can probably set the focus back to it.
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: seting focus (any)

    What I have is a subroutine that just checks the number and a public variable that becomes true if the number fails. When the subroutine is finished the textbox continues to run its code and has an if statement like so

    if Num_out_of_range = true then
    Textbox.text=""
    end if

    where num_out_of_range was the public variable that was checked in the subroutine.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: seting focus (any)

    If that code clears the textbox, then the following should set the focus to it:

    <pre> if Num_out_of_range = true then
    Textbox.text=""
    Textbox.SetFocus
    end if
    </pre>


    If that does not work, then I think we are going to need to see a copy of your workbook.
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: seting focus (any)

    Try this:

    <pre>Private Sub txtYourTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim MsgText As String, MsgType As Integer, MsgTitle As String

    With txtYourTextBox

    If Not IsNumeric(.Text) Then
    MsgText = "The entry is not a number." & vbCrLf & vbCrLf & _
    "Choose OK to re-enter, or Cancel" & vbCrLf & _
    "to continue without entering a number."

    MsgType = vbOKCancel + vbExclamation + vbDefaultButton1
    MsgTitle = "Your App"
    If MsgBox(MsgText, MsgType, MsgTitle) = vbOK Then ' User chose OK
    .SelStart = 0
    .SelLength = Len(.Text)
    Cancel = True ' Keep focus
    Exit Sub

    Else ' User chose Cancel
    .Text = ""
    Cancel = False ' Let focus go to next control by tab order
    Exit Sub

    End If ' MsgBox(MsgText, MsgType, MsgTitle) = vbOK

    Else ' Entry is numeric, put whatever other processing here

    End If ' Not IsDate(.Text)

    End With ' txtDate

    End Sub ' txtYourTextBox_Exit
    </pre>


    The advantages are:
    1) The user gets a message box explaining why the entry was not valid.
    2) The user gets the choice of re-entering or continuing without an entry for now
    3) If the user chooses to re-enter, the rpevious entry is visible, so they can figure out what went wrong
    4) The previous entry is selected entirely, so when the user starts typing a new entry, the old one is erased (no need to backspace or delete).

    If the check for numeric/non-numeric is not too complicated, it might be better to keep it in the code for the text box, so you don't have to worry about passing parameters, etc.

    The key to keeping focus is Cancel = True

Posting Permissions

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