Identify & edit code

[ipbr21054]

Morning,

I am not sure which part of the code below adds a following digit of 1 each time the same name is entered.
If the name exists then a 2 would be added to the end of that name.
Should then that name be entered again then this time it is followed by 3,in the future 4,5,6 etc etc

Can we identify & edit the part of the code that applies this as i would like to have it apply 002 or 003 etc etc as opposed to the 2,3 etc that is currently happening.

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)    Dim fndRng As Range, findString As String, i As Integer


If Me.TextBox2.Value = "" Then Exit Sub
findString = Me.TextBox2.Value


With Sheets("POSTAGE").Range("B:B")
    Set fndRng = .Find(What:=findString, LookIn:=xlValues, lookat:=xlWhole, _
                   SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                   
    If Not fndRng Is Nothing Then
        'what was entered already exists - alter the name until not found
        For i = 2 To 20
            findString = Me.TextBox2.Value & " " & i
            Set fndRng = .Find(What:=findString, LookIn:=xlValues, lookat:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If fndRng Is Nothing Then Exit For
        Next i
        
        'message saying what name should be
        'MsgBox "The name to use is " & findString
        
        'enter that name into textbox 2
        With Me.TextBox2
            .Value = findString
            .SelStart = 0
            .SelLength = Len(.Text)
            .SetFocus
        End With
        
        'cancel moving out of text box
        Cancel = True
    End If
End With

 

[Joe4]

I think the part in red below is the part you need to change:

        With Me.TextBox2
[COLOR=#ff0000]            .Value = findString[/COLOR]
            .SelStart = 0
            .SelLength = Len(.Text)
            .SetFocus
        End With

Try changing the line in red to:

[COLOR=#FF0000]            .Value = FORMAT(findString,"000")[/COLOR]

 

[Special-K99]

My answer is wrong, go with Joe4's solution, looks better my botchup at solving this.

 

[footoo]

Maybe :

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

 

[ipbr21054]

Thanks,

Joe the name entered stil come out like 2,3,4 etc etc not 002 003 004

 

[Joe4]

Hmmm, I don't work much with TextBox's on Forms.
Maybe try:

[COLOR=#FF0000] .Text = FORMAT(findString,"000")[/COLOR]

.Value may be coercing it to a number, which would drop the leading zeroes.

 

[ipbr21054]

I have forgot to advise that the existing name needs to be kept and not replaced.
So if i had JOE 2 and joe purchased again i would not replace the JOE 2 with JOE 3 but to keep JOE 2 and then the new entry would be JOE 3

JOE your code replaced the existing name with JOE 2
My fault as i did not explain correctly

I would then end up with a database of
JOE 002
JOE 003
JOE 004
JOE 005

 

[Joe4]

To clarify, what does the existing data look like, and what are you entering into the Text Box?

 

[ipbr21054]

So my worksheet is a list of transactions and in column B is the customers name.
So over time i will have a large list where the same customer makes a purchase & added to the worksheet via a userform.
At present if i sort the list i see the below example.
JOE 2
JOE 3
JOE 4
When it reaches JOE 10 the order is then all over the place due to NO leading zeros etc,hence why i am trying to have it like JOE 002 JOE 003 etc

On the userform in TextBox2 i would enter JOE the code quickly looks at the list and sees that the last entry for JOE is JOE 7 so the userform textbox changes the typed JOE to JOE 8
I then complete the rest of the form & the data from the form is sent to the worksheet.

Over time when i enter the customers name in TextBox2 i dont know if they are entered 1,2,3,4 times etc on the worksheet hence this code doing it for me.

Do this answer your question ?

Thanks

Basically do what the code is currently doing but somehow format it,so dont just add a 2 or 3 after an already typed name into TextBox2 BUT add 002,003 etc

 

[footoo]

Have you tried post #4 ?