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

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,832
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi,
I see now.
As opposed to proceeding i would rather see a msg box saying the cell cant be left blank etc
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Thanks,
Not sure what happened but the code & its operation were reversed but now ive sorted it and working well.

Many Thanks
 
Upvote 0
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
 
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
369
ipbr21054
I
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
212
ipbr21054
I
J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
372
Rhodie72
Rhodie72
I
  • Question Question
Saved pdf from worksheet doesnt show all the page
Replies
6
Views
294
ipbr21054
I
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
345
bdt
B

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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