Make N/A exempt from duplicate code check before save to worksheet

[ipbr21054]

Morning,

I am using the code supplied below.
The code checks to make sure that the value entered in TextBox4 doesnt already exist on my worksheet before it then makes the save.
If there is a matching invoice number alreay then i see the msgbox show up advising me "Already Exists"

I would like this to only apply to numbers as sometimes i now need to enter N/A
Obviously only 1 instance of N/A will allow me to make the save.

So can we some how please make N/A exempt from this check & thus allow it to be saved.

Private Sub CommandButton1_Click()

    With Sheets("DATABASE")
    
    If Not Application.CountIf(.Columns(16), Me.TextBox4.Text) > 0 Then
     Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
     Range("A6").Select
     Range("A6:Q6").Borders.LineStyle = xlContinuous
     Range("A6:Q6").Borders.Weight = xlThin
     Range("A6:Q6").Interior.ColorIndex = 6
     Range("M6") = Date
     Range("$Q$6").Value = "'NO NOTES FOR THIS CUSTOMER"
     Range("$Q$6").HorizontalAlignment = xlCenter
     
    .Range("A6").Value = Me.TextBox1.Text
    .Range("B6").Value = Me.ComboBox1.Text
    .Range("C6").Value = Me.ComboBox2.Text
    .Range("D6").Value = Me.ComboBox3.Text
    .Range("E6").Value = Me.ComboBox4.Text
    .Range("F6").Value = Me.ComboBox5.Text
    .Range("G6").Value = Me.ComboBox6.Text
    .Range("H6").Value = Me.ComboBox7.Text
    .Range("I6").Value = Me.ComboBox8.Text
    .Range("J6").Value = Me.ComboBox9.Text
    .Range("K6").Value = Me.ComboBox10.Text
    .Range("L6").Value = Me.ComboBox11.Text
    .Range("M6").Value = Me.TextBox2.Text
    .Range("N6").Value = Me.ComboBox12.Text
    .Range("O6").Value = Me.TextBox3.Text
    .Range("P6").Value = Me.TextBox4.Text
    .Range("Q6").Value = Me.TextBox5.Text
    
    Else
     With Me.TextBox4
         MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "Duplicate Invoice Number"
        .Value = "": .SetFocus
    End With
    Exit Sub
End If
End With


Dim ctrl As MSForms.Control
 
For Each ctrl In Me.Controls
    Select Case True
                Case TypeOf ctrl Is MSForms.TextBox
            ctrl.Value = ""
        Case TypeOf ctrl Is MSForms.combobox
            ctrl.Value = ""
    End Select
    
Next ctrl
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    
    TextBox2.Value = Now
    TextBox2 = Format(TextBox2.Value, "dd/mm/yyyy")
    TextBox1.SetFocus
    TextBox5.Value = "NO NOTES FOR THIS CUSTOMER"


End Sub

 

[JustynaMK]

Hi, no need to apologize!

Try modifying your first IF statement:

If Not Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 Or Me.ComboBox13.Text = "N/A" [COLOR=#ff0000]And IsNumeric(Me.ComboBox13.Text) [/COLOR]Then

This additional check will verify if you are entering a numeric value.

 

[ipbr21054]

Hi,
That still allows say gkjgkuy to be transfered to the worksheet.

I was thinking of adding another ElseIf statement ?

Basically if N/A then transfer Else msg "ONLY N/A OR NUMBERS PLEASE"

                ElseIf Len(Me.ComboBox13.Text) = 0 Then
            With Me.ComboBox13
                 MsgBox "You Must Enter An Invoice Number", vbCritical, "INVOICE NUMBER FIELD EMPTY MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        Else
            With Me.ComboBox13
                MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "DUPLICATE INVOICE NUMBER MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        End If
    End With


Dim ctrl As MSForms.Control
 
For Each ctrl In Me.Controls
    Select Case True
                Case TypeOf ctrl Is MSForms.TextBox
            ctrl.Value = ""
        Case TypeOf ctrl Is MSForms.combobox
            ctrl.Value = ""
    End Select
    
Next ctrl
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL TRANSFER MESSAGE"
    
    TextBox2.Value = Now
    TextBox2 = Format(TextBox2.Value, "dd/mm/yyyy")
    TextBox1.SetFocus
    TextBox5.Value = "NO NOTES FOR THIS CUSTOMER"


End Sub

So it would work like a password.
Only Numerical numbers or N/A would allow the transfer to continue otherwise anything else would see the msg

 

[JustynaMK]

Oops sorry, I see where my mistake was. We need to add brackets to our first If statement:

If (Not Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 Or Me.ComboBox13.Text = "N/A") And IsNumeric(Me.ComboBox13.Text) Then

Give it a try and let me know if it's any better.

 

[ipbr21054]

That now catches the jilnf,drsrysjmp but shows the message "Invoice Number jilnf,drsrysjmp already Exists"

Am i now splitting hairs as trying to get its own msg shown ?

 

[JustynaMK]

It's quite weird but let's not give up! How about taking a step back and just testing the Input against different outcomes, just by using MsgBox command. If it works, then you can expand each part with relevant events.

Private Sub CommandButton1_Click()
    With Sheets("DATABASE")
        If Len(Me.ComboBox13.Text) = 0 Then
            MsgBox "Empty String"
        ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 Then
            MsgBox "Duplicate Invoice Number (check column 'P')"
        ElseIf Not Me.ComboBox13.Text = "N/A" And IsNumeric(Me.ComboBox13.Text) Then
            MsgBox "Valid Input - Continue"
        Else
            MsgBox "The string is not a number or is N/A"
        End If
    End With
End Sub

 

[ipbr21054]

I’m putting this in my existing code as a text and removing what’s currently there ?

I did note and reported in a post or two back that the msg was being shown for the wrong thing.
Example I seen a message about invoice number exists when I put N/A

I sorted this out so now I am where I am.
Strange it did that but it did.

 

[JustynaMK]

Yes, I think it would be a good test if you replace your existing code with the one that I just provided. This simplified code will test if your input displays the correct MsgBox. Once we confirm it is doing exactly what you want it to do, then you can replace MsgBox parts with your code.

 

[ipbr21054]

Hi,
I have placed this only on CommandButton1.

Private Sub CommandButton1_Click()    With Sheets("DATABASE")
        If Len(Me.ComboBox13.Text) = 0 Then
            MsgBox "Empty String"
        ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 Then
            MsgBox "Duplicate Invoice Number (check column 'P')"
        ElseIf Not Me.ComboBox13.Text = "N/A" And IsNumeric(Me.ComboBox13.Text) Then
            MsgBox "Valid Input - Continue"
        Else
            MsgBox "The string is not a number or is N/A"
        End If
    End With


End Sub

First i start with numbers that are already in use.
22 then 71 then 100.
Msg was "Duplicate Invoice Number (check column 'P')"

I then type N/A
Msg was "The string is not a number or is N/A"

I then type anything.
vflimnnhdbhsk
Msg was Msg was "Duplicate Invoice Number (check column 'P')"

I then left it empty.
Msg was "Empty String"

When this is sorted i am looking for 3 different Msg box

If a number exists then "Invoice Number 100 already Exists"

If left blank then "Please Enter An Invoice Number"

If gjkhbgfrjeujgd then "Only An Invoice Number Or N/A Is Allowed"

Thanks

 

[ipbr21054]

Please wait a moment

 

[ipbr21054]

Ok please continue.

The only issue i see is N/A
As the Msg pops up advising me Duplicate invoice number