Userform to worksheet values followed by 3 digits

[ipbr21054]

I have a userform whenre i send the values to my worksheet.
Userform TextBox8 is the customers name.
When i transfer values to the worksheet the Textbox8 value is entered in the cell A5
Customers name are like so.
TOM JONES 001

If i have a returning customer then we add to there existing 001 to 002 then 003 etc

The transfer to worksheet code below works fine apart from checking column A to see if the customer is present & if not it should be 001 BUT if a returning customer then add 002 003 acordingly
"SEE FIND CUSTOMER CODE BELOW"

TRANSFER TO WORKSHEET CODE

Private Sub CommandButton1_Click()
Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A5:AC5").Borders.LineStyle = xlContinuous
Range("A5:AC5").Borders.Weight = xlThin
Range("A5:AC5").Interior.ColorIndex = 6
Range("A5:AC5").RowHeight = 25
Range("$Q$5").HorizontalAlignment = xlCenter
Sheets("DATABASE").Range("B5").Select
Range("X5").Value = "N/A"
Range("O5").NumberFormat = "$#,##0.00"

Cancel = 0
If TextBox8.Text = "" Then
    Cancel = 1
    MsgBox "YOU MUST ENTER A CUSTOMERS NAME", vbCritical, "DATABASE USER FORM NAME TRANSFER"
    TextBox8.SetFocus
    
End If

If Cancel = 1 Then
Exit Sub
End If

Dim i As Long
Dim x As Long
Dim ctrl As Control
Dim lastRow As Long
    
With ThisWorkbook.Worksheets("DATABASE")
    .Range("A5").Value = TextBox8.Text

End With
    
    
  With ThisWorkbook.Worksheets("DATABASE")
    
    ThisWorkbook.Worksheets("DATABASE").Range("A5") = Me.TextBox8.Text ' CUSTOMERS NAME
    ThisWorkbook.Worksheets("DATABASE").Range("B5") = Me.ComboBox1.Text ' REGISTRATION NUMBER
    ThisWorkbook.Worksheets("DATABASE").Range("C5") = Me.ComboBox2.Text ' BLANK USED
    ThisWorkbook.Worksheets("DATABASE").Range("D5") = Me.ComboBox3.Text ' VEHICLE
    ThisWorkbook.Worksheets("DATABASE").Range("E5") = Me.ComboBox4.Text ' BUTTONS
    ThisWorkbook.Worksheets("DATABASE").Range("F5") = Me.ComboBox5.Text ' ITEM SUPPLIED
    ThisWorkbook.Worksheets("DATABASE").Range("G5") = Me.ComboBox6.Text ' TRANSPONDER CHIP
    ThisWorkbook.Worksheets("DATABASE").Range("H5") = Me.ComboBox7.Text ' JOB ACTION
    ThisWorkbook.Worksheets("DATABASE").Range("I5") = Me.ComboBox8.Text ' PROGRAMMER USED
    ThisWorkbook.Worksheets("DATABASE").Range("J5") = Me.ComboBox9.Text ' KEY CODE
    ThisWorkbook.Worksheets("DATABASE").Range("K5") = Me.ComboBox10.Text ' BITING
    ThisWorkbook.Worksheets("DATABASE").Range("L5") = Me.ComboBox11.Text ' CHASIS NUMBER
    ThisWorkbook.Worksheets("DATABASE").Range("N5") = Me.ComboBox12.Text ' VEHICLE YEAR
    ThisWorkbook.Worksheets("DATABASE").Range("R5") = Me.TextBox1.Text ' ADDRESS 1st LINE
    ThisWorkbook.Worksheets("DATABASE").Range("S5") = Me.TextBox2.Text ' ADDRESS 2nd LINE
    ThisWorkbook.Worksheets("DATABASE").Range("T5") = Me.TextBox3.Text ' ADDRESS 3rd LINE
    ThisWorkbook.Worksheets("DATABASE").Range("U5") = Me.TextBox4.Text ' ADDRESS 4TH LINE
    ThisWorkbook.Worksheets("DATABASE").Range("V5") = Me.TextBox5.Text ' POST CODE
    ThisWorkbook.Worksheets("DATABASE").Range("W5") = Me.TextBox6.Text ' CONTACT NUMBER
    ThisWorkbook.Worksheets("DATABASE").Range("AA5") = Me.ComboBox13.Text ' SUPPLIER
    ThisWorkbook.Worksheets("DATABASE").Range("AB5") = Me.TextBox7.Text ' PART NUMBER
    ThisWorkbook.Worksheets("DATABASE").Range("AC5") = Me.ComboBox14.Text ' PAYMENT TYPE
    End With
    Unload DatabaseRepeatCustomer
End Sub

My problem is trying to include the code below to the code above.
1 I see it shoes BeforeUpdate.
2 I see two instances of Dim i As

So im lost & need some help please

FIND CUSTOMER CODE

Private Sub TextBox8_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim fndRng As Range

Dim findString As String

Dim i As Integer

Dim wsPostage As Worksheet



findString = Me.TextBox8.Value

If Len(findString) = 0 Then Exit Sub

Set wsPostage = ThisWorkbook.Worksheets("DATABASE")

i = 1

Do

Set fndRng = Nothing

Set fndRng = wsPostage.Range("A:A").Find(what:=findString & Format(i, " 000"), _

LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _

SearchDirection:=xlNext, MatchCase:=False)

If Not fndRng Is Nothing Then

i = i + 1

Cancel = True

End If

Loop Until fndRng Is Nothing

Me.TextBox8.Value = findString & Format(i, " 000")

Cancel = False

End Sub

 

[DanteAmor]

Do the following:

Remove the code from this event:
Private Sub TextBox8_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Replace your commandbutton1 code with the following code:

Private Sub CommandButton1_Click()
  Dim cName As String
  Dim n As Long
  
  cName = TextBox8.Text
  If cName = "" Then
    MsgBox "YOU MUST ENTER A CUSTOMERS NAME", vbCritical, "DATABASE USER FORM NAME TRANSFER"
    TextBox8.SetFocus
    Exit Sub
  End If

  With ThisWorkbook.Worksheets("DATABASE")
    n = WorksheetFunction.CountIf(.Range("A:A"), cName & " ???")
    
    cName = cName & IIf(n = 0, " 001", Format(n + 1, " 000"))
    
    .Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("A5:AC5").Borders.LineStyle = xlContinuous
    .Range("A5:AC5").Borders.Weight = xlThin
    .Range("A5:AC5").Interior.ColorIndex = 6
    .Range("A5:AC5").RowHeight = 25
    .Range("$Q$5").HorizontalAlignment = xlCenter
    .Range("X5").Value = "N/A"
    .Range("O5").NumberFormat = "$#,##0.00"
    
    .Range("A5") = cName                ' CUSTOMERS NAME
    .Range("B5") = Me.ComboBox1.Text    ' REGISTRATION NUMBER
    .Range("C5") = Me.ComboBox2.Text    ' BLANK USED
    .Range("D5") = Me.ComboBox3.Text    ' VEHICLE
    .Range("E5") = Me.ComboBox4.Text    ' BUTTONS
    .Range("F5") = Me.ComboBox5.Text    ' ITEM SUPPLIED
    .Range("G5") = Me.ComboBox6.Text    ' TRANSPONDER CHIP
    .Range("H5") = Me.ComboBox7.Text    ' JOB ACTION
    .Range("I5") = Me.ComboBox8.Text    ' PROGRAMMER USED
    .Range("J5") = Me.ComboBox9.Text    ' KEY CODE
    .Range("K5") = Me.ComboBox10.Text   ' BITING
    .Range("L5") = Me.ComboBox11.Text   ' CHASIS NUMBER
    .Range("N5") = Me.ComboBox12.Text   ' VEHICLE YEAR
    .Range("R5") = Me.TextBox1.Text     ' ADDRESS 1st LINE
    .Range("S5") = Me.TextBox2.Text     ' ADDRESS 2nd LINE
    .Range("T5") = Me.TextBox3.Text     ' ADDRESS 3rd LINE
    .Range("U5") = Me.TextBox4.Text     ' ADDRESS 4TH LINE
    .Range("V5") = Me.TextBox5.Text     ' POST CODE
    .Range("W5") = Me.TextBox6.Text     ' CONTACT NUMBER
    .Range("AA5") = Me.ComboBox13.Text  ' SUPPLIER
    .Range("AB5") = Me.TextBox7.Text    ' PART NUMBER
    .Range("AC5") = Me.ComboBox14.Text  ' PAYMENT TYPE
  End With
  Unload DatabaseRepeatCustomer
End Sub

Functioning:
Capture all the data in the controls, when you press the commandbutton1, the code "searches" for how many clients exist with that name, if it is 0, then it creates the client & "001", if the client already exists then it adds a 1 to it.

 

[ipbr21054]

Many thanks works a treat