TextBox validation working but one line of code is not executed...

[albertc30]

Hello everyone.

I am back again.

The code bellow is working to an extent. However, when I click OK to the Error I get, it clears the field BUT the customer name field (tbnbna) does not get the setfocus.

Private Sub tbnbna_AfterUpdate()    If Application.WorksheetFunction.CountIf(Range("A:A"), Me.tbnbna) > 0 Then
        MsgBox "Customer already exists!"
        Me.tbnbna.Value = "" 'This line works...
[COLOR=#ff0000]        Me.tbnbna.SetFocus 'But this line of code is not working as the cursor is moving along to the next textbox![/COLOR]
        adcubton.Enabled = False 'This line also works...
    Exit Sub
        
    End If
        adcubton.Enabled = True
End Sub


Private Sub tbnbna_Change()
     tbnbna.Text = UCase(tbnbna.Text)
End Sub

Now, on another note, is it good practice to have this code checking for the duplicated entries on the field itself, or is it better practice to have it when the form is executed via the command botton?

The reason I am currently doing it this way to not waste the user's time filling the entire form only then to get an error. On the other hand, I suppose even upon error with duplicated customer name, I guess it would be just the case to setfocus to that field and entering a different name and then submitting the form which would be otherwise already filled in.

Any help, as always, much appreciated.

Cheers.

 

[AlphaFrog]

Use the _Exit event instead

Private Sub tbnbna_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.tbnbna.Value <> "" Then
    If Application.WorksheetFunction.CountIf(Range("A:A"), Me.tbnbna) > 0 Then
        MsgBox "Customer already exists!"
        Me.tbnbna.Value = ""
        Cancel = True [COLOR=#008000]'Do not exit the textbox[/COLOR]
        adcubton.Enabled = False 'This line also works...
    Else
         adcubton.Enabled = True       
    End If
End If
End Sub

 

[albertc30]

Hi mate.

It now works a treat!

Thanks for your time, much appreciated.

Cheers.

 

[albertc30]

Hi, again...

The above code works fine.

But on test, say I already have a customer with name CAFÉ PLAZZA.

If user enters CAFE PLAZZA as a new customer, the duplicate will not be found.

Now I was thinking, can we limit this to the textbox data input and not allow special symbols or will it be easier to implement this on the exit of the button?

Private Sub tbnbna_Change()     tbnbna.Text = UCase(tbnbna.Text)
     
      [COLOR=#ff0000]  'trying code bellow but when it returns the error, it clears the textbox but it returns the error a second time. I'm lost...[/COLOR]
     
     Dim strStrings As String, LastLetter As String


    Application.EnableEvents = False
    LastLetter = Right(tbnbna, 1)
    strStrings = "\/~`|@#$%^&*()_+!<>?:;'"""
    If InStr(1, strStrings, LastLetter) > 0 Then
    MsgBox LastLetter & " [COLOR=#ff0000]not allowed[/COLOR]"
    'tbnbna = Left(tbnbna, Len(tbnbna) - 1)
    tbnbna.Text = ""
    
    Exit Sub
    
    End If
    Application.EnableEvents = True
     
        'trying the code above
     
End Sub


Private Sub tbnbna_Exit(ByVal Cancel As MSForms.ReturnBoolean)


    If Me.tbnbna.Value <> "" Then
        If Application.WorksheetFunction.CountIf(Range("A:A"), Me.tbnbna) > 0 Then
            Beep
            MsgBox "Customer already exists!"
            Me.tbnbna.Value = ""
            Cancel = True 'Do not exit the textbox
            adcubton.Enabled = False 'This line also works...
        
               
              
                
        Else
         adcubton.Enabled = True
         
         
         
        End If
    End If


End Sub

What would be the best way to go about this, please?

As always, any help is truly appreciated.

Much appreciated for you time.

Cheers.

 

[AlphaFrog]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] tbnbna_KeyPress([COLOR=darkblue]ByVal[/COLOR] KeyAscii As MSForms.ReturnInteger)
    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] KeyAscii [COLOR=#008000]'Typed character[/COLOR]
        [COLOR=darkblue]Case[/COLOR] 48 [COLOR=darkblue]To[/COLOR] 57, 65 [COLOR=darkblue]To[/COLOR] 90, 97 To 122    [COLOR=green]'0-9, A-Z, a-z[/COLOR]
        [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR]
            KeyAscii = 0 [COLOR=green]'Delete last character[/COLOR]
            MsgBox "Please enter standard alphanumeric characters only." & _
                   vbLf & vbLf & "0-9, A-Z, a-z", vbExclamation, "Invalid Character"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[AlphaFrog]

EDIT to the code above: Add ASCII character 32 to allow a space.

        [color=darkblue]Case[/color] [B]32,[/B] 48 [color=darkblue]To[/color] 57, 65 [color=darkblue]To[/color] 90, 97 To 122   [color=green]'Space, 0-9, A-Z, a-z[/color]

 

[albertc30]

Hi AlphaFrog;

I have tried your code but it results in nothing I'm afraid.

Many thanks.

P.S: Your first code works but yes it won't allow a space.

 

[AlphaFrog]

What does results in nothing mean?

Did you do this?

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] tbnbna_KeyPress([COLOR=darkblue]ByVal[/COLOR] KeyAscii As MSForms.ReturnInteger)
    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] KeyAscii [COLOR=#008000]'Typed character[/COLOR]
        [COLOR=darkblue]Case[/COLOR] 32, 48 [COLOR=darkblue]To[/COLOR] 57, 65 [COLOR=darkblue]To[/COLOR] 90, 97 To 122    [COLOR=green]'0-9, A-Z, a-z[/COLOR]
        [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR]
            KeyAscii = 0 [COLOR=green]'Delete last character[/COLOR]
            MsgBox "Please enter standard alphanumeric characters only." & _
                   vbLf & vbLf & "0-9, A-Z, a-z", vbExclamation, "Invalid Character"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]