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]

Can you try one more thing please:

Before your "With" statement, enter the following line:

Debug.Print Me.ComboBox13.Text

Once you run your code, please let me know the result from that statement. You will see it in your Immediate Window (Ctrl+G).

 

[ipbr21054]

Morning,
So this is the code i now have.

Private Sub CommandButton1_Click()Debug.Print Me.ComboBox13.Text
    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

I assume that as N/A is the only issue then i have type or selected N/A in ComboBo13
I then see the Msg.

In the Immediate window i see

N/A

 

[JustynaMK]

Thanks for the update. So the only issue now is with N/A, which displays "Duplicate Invoice Number" error message, is that correct? Could you please check your column P and see if it contains any N/A?
Otherwise, I have no more ideas - sorry! The only thing I can suggest is for you to share the file so that I can take a look.

 

[ipbr21054]

Hi,
Yes there is N/A in column P.
That’s why I need it allowed & not to show msg for duplicate.

Only items allowed to be transferred to worksheet are “N/A” & “Invoice Numbers that have not been used so far in column P”

 

[JustynaMK]

Ahh ok! You can remove it from column "P" as it will always be allowed (as per our ElseIf statement). Would it work for you? Or do you definitely need to keep it in column P?

 

[ipbr21054]

In column P i either need an invoice number or something else.
If left blank then staff think an invoice number was not entered.

Was else do you advise to put there should no invoice number be required

 

[JustynaMK]

I think in this case, if you wish to leave "N/A" in column "P", you can reorganise your code a little bit and use the following:

Private Sub CommandButton1_Click()Debug.Print Me.ComboBox13.Text
    With Sheets("DATABASE")
        If Len(Me.ComboBox13.Text) = 0 Then
            MsgBox "Empty String"
        [COLOR=#ff0000]ElseIf Me.ComboBox13.Text = "N/A" Or [/COLOR]IsNumeric(Me.ComboBox13.Text) Then
            MsgBox "Valid Input - Continue"
        [COLOR=#FF0000]ElseIf [/COLOR]Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 Then
            MsgBox "Duplicate Invoice Number (check column 'P')"

        Else
            MsgBox "The string is not a number or is N/A"
        End If
    End With
End Sub

The code will recognise "N/A" and will allow it to transfer (in our example - it will show MsgBox "Valid Input - Continue"). Let me know the results.

 

[ipbr21054]

I think that has made it worse.

Empty cell i see msg Empty String

I type 100 "of which is already in column p" i see msg Valid Input Continue

I type/select N/A i see msg Valis Input Continue

I type 999 "of which is not in column P i see msg Valid Input Continue

On clicking OK on the msg box it just closed as opposed to sending data to worksheet

 

[JustynaMK]

Uh that doesn't sound good. Any chance you could upload your file and share it?

 

[JustynaMK]

Thanks for sharing the file! It really helped me test all the inputs. Hopefully this modification will do the trick:

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 And Not Me.ComboBox13.Text = "N/A" Then
            MsgBox "Duplicate Invoice Number (check column 'P')"
        ElseIf IsNumeric(Me.ComboBox13.Text) Or Me.ComboBox13.Text = "N/A" Then
            MsgBox "Valid Input - Continue"
        Else
            MsgBox "The string is not a number"
        End If
    End With
End Sub