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

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,071
Office Version
2019
Platform
Windows
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.

Code:
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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
Use the _Exit event instead

Code:
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
 
Last edited:

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,071
Office Version
2019
Platform
Windows
Hi mate.

It now works a treat!

Thanks for your time, much appreciated.

Cheers.
 

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,071
Office Version
2019
Platform
Windows
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?

Code:
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.
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
Code:
[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]
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
EDIT to the code above: Add ASCII character 32 to allow a space.

Code:
        [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

Well-known Member
Joined
May 7, 2012
Messages
1,071
Office Version
2019
Platform
Windows
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.
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
What does results in nothing mean?

Did you do this?
Code:
[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]
 

Watch MrExcel Video

Forum statistics

Threads
1,101,996
Messages
5,484,089
Members
407,430
Latest member
sgoldman

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top