Identify & edit code

ipbr21054

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

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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]
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
My answer is wrong, go with Joe4's solution, looks better my botchup at solving this.
 
Last edited:

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,049
Office Version
  1. 2016
Platform
  1. Windows
Maybe :
Code:
findString = Me.TextBox2.Value & Format(i, " 000")
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,636
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Thanks,

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,636
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
To clarify, what does the existing data look like, and what are you entering into the Text Box?
 

ipbr21054

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

Well-known Member
Joined
Sep 21, 2016
Messages
3,049
Office Version
  1. 2016
Platform
  1. Windows
Have you tried post #4 ?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,105
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top