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

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,226
Office Version
  1. 2007
Platform
  1. Windows
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
 
Sort by date Sort by votes
Hi,
The last reply seems to work ok.
Below is the original code of which the above now needs to be inserted into it.

Please can you advise now the full code with the above so i can then just copy & paste.

Thanks very much for your time.

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


     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.ComboBox13.Text
    .Range("Q6").Value = Me.TextBox5.Text
    
            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
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi,

Give it a try:

Code: 
Private Sub CommandButton1_Click()
    With Sheets("DATABASE")
        If 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
        ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not Me.ComboBox13.Text = "N/A" Then
            With Me.ComboBox13
                MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "DUPLICATE INVOICE NUMBER MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        ElseIf IsNumeric(Me.ComboBox13.Text) Or Me.ComboBox13.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.ComboBox13.Text
            .Range("Q6").Value = Me.TextBox5.Text
        Else
            With Me.ComboBox13
                MsgBox "Invoice " & .Text & " Is Not A Number", vbCritical, "INVALID INVOICE"
                .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
 
Upvote 0
Looks ok but will use it for a few days etc

Thanks very much for the help
 
Upvote 0
:) awesome - hope it will work for you.
Have a nice week!
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
85
ipbr21054
I
I
  • Solved
Userform to worksheet values followed by 3 digits
Replies
2
Views
185
ipbr21054
I
I
  • Question
Move existing row of value to another selected row
Replies
1
Views
131
ipbr21054
I
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
398
Legacy 352679
L
M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
155
mikefisher
M

Forum statistics

Threads
1,214,911
Messages
6,122,198
Members
449,072
Latest member
DW Draft

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top