Set focus back to seme TexBox

[Sahak]

Hi all,

On UserForm I have textBox were user should enter school name, I would like, if the name already exists, to get message that the name exists & Focus stays in same TextBox (not move to the next control)
Thank you in advance.

 

[AlphaFrog]

Use the TextBox1_Exit event procedure. Maybe something like this. (you didn't explain how to test if the name exists with your data. The IF statement is psudo-code)

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] TextBox1_Exit([color=darkblue]ByVal[/color] Cancel [color=darkblue]As[/color] MSForms.ReturnBoolean)
    [color=darkblue]If[/color] Name_Exists [color=darkblue]Then[/color]
        MsgBox TextBox1.Text, vbExclamation, "Name Exists"
        TextBox1.Text = ""
        Cancel = [color=darkblue]True[/color] [color=green]'Don't exit the textbox[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[Sahak]

Yes!!! Thank you very much.

 

[Yongle]

Perhaps something like this ...

Amend to match your own names and ranges
- textbox name = tb_School
- list of schools = Sheets("Schools").Columns("A")

Private Sub tb_School_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim List As Range, Sch As Control
    Set List = Sheets("Schools").Columns("A")
    Set Sch = tb_School
    If Sch.Value = "" Then Exit Sub
    
    If WorksheetFunction.CountIf(List, Sch.Text) > 0 Then
        MsgBox Sch.Text, vbExclamation, "DUPLICATE"
        Cancel = True
        Sch.Text = ""
    End If

End Sub

 

[Sahak]

Thank you Yongle very much.