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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
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.




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

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
630
Office Version
365, 2013
Platform
Windows
Hi,

I think simply modifying the first line of your "If" statement will do the trick:

Old version

Code:
If Not Application.CountIf(.Columns(16), Me.TextBox4.Text) > 0 Then
New version
Code:
If Not Application.CountIf(.Columns(16), Me.TextBox4.Text) > 0 Or Me.TextBox4.Text = "N/A" Then
Give it a try and let me know if it works for you.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Hi,
That worked fine.
One thing i did notice that if you dont type anything in the same text box then i also see the msg "Invoice Number Already Exists"

This must of been happening before but i didnt notice it.

Do you see why it might be doing this ?

Thanks

Basically its either got to be a number or N/A nothing else
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
630
Office Version
365, 2013
Platform
Windows
It happens because your 16th column also contains blank cells, so when VBA checks your blank input against column "P" it marks it as a duplicate.

Try adding another OR statement in order to force your code to proceed with blank inputs:

Code:
If Not Application.CountIf(.Columns(16), Me.TextBox4.Text) > 0 Or Me.TextBox4.Text = "N/A" [COLOR=#ff8c00]Or Len(Me.TextBox4.Text) = 0[/COLOR] Then
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Hi,
I see now.
As opposed to proceeding i would rather see a msg box saying the cell cant be left blank etc
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
I have tried to add an ELSEIF etc to try and pop up a msgbox should the invoice field be left empty etc.
Below code didnt work for me,i understand its incorrect but do see how to correct it


Code:
Private Sub CommandButton1_Click()

    With Sheets("DATABASE")
    
    If Not Application.CountIf(.Columns(16), Me.TextBox4.Text) > 0 Or Me.TextBox4.Text = "N/A" 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
    
    ElseIf
     With Me.TextBox4
         MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "Duplicate Invoice Number"
        .Value = "": .SetFocus
    Else
     With Me.TextBox4
         MsgBox "Please Enter Invoice Number", vbCritical, "INVOICE NUMBER FIELD IS EMPTY MESSAGE"
    End With
    Exit Sub
End If
End With
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
630
Office Version
365, 2013
Platform
Windows
We're almost there - ElseIf statement also requires some conditions (simply stating "ElseIf" doesn't work). Additionally, you have "With" statement without "End With". I also recommend using indentation as this will make your life so much easier. Please see below (I removed your whole "Range" block from it and replaced it with a comment just for clarity; simply replace this part with your original code).

Code:
Private Sub CommandButton1_Click()
    With Sheets("DATABASE")
        [COLOR=#ff0000]If[/COLOR] Not Application.CountIf(.Columns(16), Me.TextBox4.Text) > 0 Or Me.TextBox4.Text = "N/A" [COLOR=#ff0000]Then[/COLOR]
[COLOR=#0000ff]            'the whole "Range" block[/COLOR]
[COLOR=#ff0000]        ElseIf Len(Me.TextBox4.Text) = 0 Then[/COLOR]
            [COLOR=#0000ff]With [/COLOR]Me.TextBox4
                 MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "Duplicate Invoice Number"
                .Value = "": .SetFocus
[COLOR=#0000ff]            End With[/COLOR]
[COLOR=#ff0000]        Else[/COLOR]
            With Me.TextBox4
                MsgBox "Please Enter Invoice Number", vbCritical, "INVOICE NUMBER FIELD IS EMPTY MESSAGE"
            End With
            Exit Sub 'I think this line can be removed as VBA code will end anyway at this point
[COLOR=#ff0000]        End If[/COLOR]
    End With
End Sub
I hope you will find it helpful. Let me know if anything is unclear.
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Thanks,
Not sure what happened but the code & its operation were reversed but now ive sorted it and working well.

Many Thanks
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
630
Office Version
365, 2013
Platform
Windows
:) glad it all worked for you!

Take care.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Sorry im back with this.

The code in use in supplied below.
TextBox4 has now been changed to ComboBox13

The way it should work is a Number or N/A entered into ComboBox13 will allow a transfer, Otherwise do not transfer the userform data to worksheet.

Everything works fine apart from if you type just anything like htkmdpfzg it accepts this then is transfered to the worksheet.
This random text should be flagged up & show the user a msg box.

I would like only a Number or N/A for transfer, anything other than a Number or N/A then show the user a msg box


Code:
Private Sub CommandButton1_Click()

    With Sheets("DATABASE")
    
    If Not Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 Or Me.ComboBox13.Text = "N/A" Then
          **LONG SECTION OF CODE RANGE REMOVED FOR CLARITY**

          
            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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,081
Messages
5,466,528
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top