Identify & edit code

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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.


Code:
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I think the part in red below is the part you need to change:
Code:
        With Me.TextBox2
[COLOR=#ff0000]            .Value = findString[/COLOR]
            .SelStart = 0
            .SelLength = Len(.Text)
            .SetFocus
        End With
Try changing the line in red to:
Code:
[COLOR=#FF0000]            .Value = FORMAT(findString,"000")[/COLOR]
 
Upvote 0
My answer is wrong, go with Joe4's solution, looks better my botchup at solving this.
 
Last edited:
Upvote 0
Maybe :
Code:
findString = Me.TextBox2.Value & Format(i, " 000")
 
Upvote 0
Thanks,

Joe the name entered stil come out like 2,3,4 etc etc not 002 003 004
 
Upvote 0
Hmmm, I don't work much with TextBox's on Forms.
Maybe try:
Code:
[COLOR=#FF0000] .Text = FORMAT(findString,"000")[/COLOR]
.Value may be coercing it to a number, which would drop the leading zeroes.
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
To clarify, what does the existing data look like, and what are you entering into the Text Box?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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