Working code needs a new piece of code added

[ipbr21054]

Good Morning,
I have the working code supplied below but would like an extra piece of code added.

This is how it currently works.
I use a form to transfer data to a worksheet with the use of pressing the CommandButton1
The worksheet is called DATABASE
On the form is TextBox4 were i would enter an invoice number.
This value is then inserted to the DATABASE worksheet in cell P6

There is nothing in place which would stop a duplicate invoice number being transfered / inserted to the sheet.
So this is where i would like the new piece of code added.

This is how it should then work with the new code.

I would press the transfer button.
This new code would then check for a match in the DATABASE sheet in column P
If no match is found then continue with the transfer.
If a match is found then show msgbox saying invoice say 123 exists.
Pressing ok would clear TextBox4 waiting for me to then insert a number again.

Private Sub CommandButton1_Click()

    With Sheets("DATABASE")
     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
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"
    
    TextBox2.Value = Now
    TextBox2 = Format(TextBox2.Value, "dd/mm/yyyy")
    TextBox1.SetFocus


End Sub

 

[dmt32]

hi,
try

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 .Text & Chr(10) & "Invoice Number Exists", 16, "Duplicate 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"
    
    TextBox2.Value = Now
    TextBox2 = Format(TextBox2.Value, "dd/mm/yyyy")
    TextBox1.SetFocus




End Sub

Dave

 

[ipbr21054]

Thanks,
As opposed to completing the form to see what it looks like & title etc how can i run the coder which will then just pop up the msgbox.

Thanks

 

[dmt32]

Thanks,
As opposed to completing the form to see what it looks like & title etc how can i run the coder which will then just pop up the msgbox.

Thanks

try

Private Sub TextBox4_Change()
        With Me.TextBox4
        If Len(.Text) > 0 Then
            If Application.CountIf(Sheets("DATABASE").Columns(16), .Text) > 0 Then
                MsgBox .Text & Chr(10) & "Invoice Number Exists", 16, "Duplicate Number"
                .Text = "": .SetFocus
            End If
        End If
        End With
End Sub

Dave

 

[ipbr21054]

Thanks for the help and advice