TextBox validation working but one line of code is not executed...
Results 1 to 8 of 8

Thread: TextBox validation working but one line of code is not executed...
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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...
            Me.tbnbna.SetFocus 'But this line of code is not working as the cursor is moving along to the next textbox!
            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.
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,047
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

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

    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 'Do not exit the textbox
            adcubton.Enabled = False 'This line also works...
        Else
             adcubton.Enabled = True       
        End If
    End If
    End Sub
    Last edited by AlphaFrog; Aug 7th, 2019 at 03:04 AM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi mate.

    It now works a treat!

    Thanks for your time, much appreciated.

    Cheers.
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

  4. #4
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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)
         
            'trying code bellow but when it returns the error, it clears the textbox but it returns the error a second time. I'm lost...
         
         Dim strStrings As String, LastLetter As String
    
    
        Application.EnableEvents = False
        LastLetter = Right(tbnbna, 1)
        strStrings = "\/~`|@#$%^&*()_+!<>?:;'"""
        If InStr(1, strStrings, LastLetter) > 0 Then
        MsgBox LastLetter & " not allowed"
        '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 by albertc30; Aug 7th, 2019 at 11:21 AM. Reason: forgot to mention...
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

  5. #5
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,047
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

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

    Code:
    Private Sub tbnbna_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
        Select Case KeyAscii 'Typed character
            Case 48 To 57, 65 To 90, 97 To 122    '0-9, A-Z, a-z
            Case Else
                KeyAscii = 0 'Delete last character
                MsgBox "Please enter standard alphanumeric characters only." & _
                       vbLf & vbLf & "0-9, A-Z, a-z", vbExclamation, "Invalid Character"
        End Select
    End Sub
    Last edited by AlphaFrog; Aug 7th, 2019 at 02:33 PM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,047
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

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

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

    Code:
            Case 32, 48 To 57, 65 To 90, 97 To 122   'Space, 0-9, A-Z, a-z
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  7. #7
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by albertc30; Aug 7th, 2019 at 05:27 PM.
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

  8. #8
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,047
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

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

    What does results in nothing mean?

    Did you do this?
    Code:
    Private Sub tbnbna_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
        Select Case KeyAscii 'Typed character
            Case 32, 48 To 57, 65 To 90, 97 To 122    '0-9, A-Z, a-z
            Case Else
                KeyAscii = 0 'Delete last character
                MsgBox "Please enter standard alphanumeric characters only." & _
                       vbLf & vbLf & "0-9, A-Z, a-z", vbExclamation, "Invalid Character"
        End Select
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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