Consulting

Results 1 to 4 of 4

Thread: TextBox SetFocus Problem on UserForm

  1. #1
    VBAX Regular
    Joined
    Apr 2018
    Posts
    19
    Location

    TextBox SetFocus Problem on UserForm

    Hi, I am having trouble with the setfocus command and I have done a bit of searching but my knowledge of vba is very little.

    I would like a number value to be entered in TextboxControl and on typing a value if the data is not in the correct format a message will appear with an OK Button. When OK is selected I would like the wrong data to be cleared and the cursor back in the TextboxControl.

    The code below is working fine except the SetFocus doesn't appear in the activate control.

    Here is the code in Module mode:
    Sub OnlyNumbers()
    
        If TypeName(ExpensesForm.ActiveControl) = "TextBox" Then
    
            With ExpensesForm.ActiveControl
                If Not IsNumeric(.Value) And .Value <> vbNullString Then
                    ExpensesForm.ActiveControl.BackColor = &HC0C0FF 'read
    
                    MsgBox "Sorry, only numbers allowed"
    
                    .Value = vbNullString
                    ExpensesForm.ActiveControl.BackColor = &HFFFFFF
                    ExpensesForm.ActiveControl.SetFocus
                End If
            End With
    
        End If
    
    End Sub
    Here is the code in Userform mode:
    Private Sub UnitCost_Change()
    Call OnlyNumbers
    End Sub
    Thanks in advance.

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello jarekmos,

    You will need to use 2 events to accomplish your goal. If you have multiple TextBoxes you want to apply this to then this can be done by Sub-Classing the TextBox controls.

    Here is an Example using TextBox1. The AfterUpdate event fires once The Enter key has been pressed or another control has been clicked. This will validate if the entry is a valid number and set the Control's Tag property to "False" if it fails. The Exit event will examine the Tag. If it is "False" it then display the error message, select the text in the control, and "Cancel" the auto-tab to the next control. This keeps the focus on the TextBox.
    Private Sub TextBox1_AfterUpdate()
        If Not IsNumeric(TextBox1.Value) Then
            TextBox1.Tag = "False"
        End If
    End Sub
    
    
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If TextBox1.Tag = "False" Then
            MsgBox "Only numbers are allowed."
            TextBox1.Tag = ""
            TextBox1.SelStart = 0
            TextBox1.SelLength = Len(TextBox1.Value)
            Cancel = True
        End If
    End Sub
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    VBAX Regular
    Joined
    Apr 2018
    Posts
    19
    Location
    Thanks Ross for your response, but it is not quite what I was looking for.

    My code works fine, so I would prefer to keep it, but have it modified a little. The problem still keeps on recurring. Whenever I get an error message and I press OK on it the cursor doesn't jump right back to the active textbox, instead I have to use my mouse to click on the textbox.

    I also have a lot of textboxs, so when I press the enter key on one textbox then the cursor would jump to the next, that is why I don't want a code specifically for one textbox.

  4. #4
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    [COLOR=#333333]Hello jarekmos,

    As I stated if you have many TextBoxes then the events can be shared using a technique known as Sub-Classing.

    NOTE: This question was also cross-posted here https://www.excelforum.com/excel-pro...-userform.html
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

Posting Permissions

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