Userforms - Textbox Autofill

[Auxie]

Hello!

Probably a super easy noob question however.. I've created a Userform which works without any issue, but I'm trying to make it a little bit faster by adding various autofills, which i've been able to do most of them but im having a bit of trouble when it comes to a reference number.

I require it to increase by 1 every time the userform is used, the reference isn't purely numbers its a mixture of letters hyphens and numbers (last few being numbers), would the code be something like

... Range("A:A")+1 ...

Or would it be something more complex?

Thanks in advance.

[Paul_Hossler]

Probably something like this.

Not too complex

Option Explicit
Sub demo()
    Dim s As String
    s = "ABD-DEF-00123"
    MsgBox s
    
    s = AddOne(s)
    MsgBox s
    
    s = AddOne(s)
    MsgBox s
    
    s = AddOne(s)
    MsgBox s
    
    s = AddOne(s)
    MsgBox s
    
End Sub

'assumes something like AAA-BBB-CCC-123 with numbers only in last position and seperated by hyphen
Function AddOne(s As String) As String
    Dim v As Variant
    Dim n As Long
    Dim i As String
    
    v = Split(s, "-")
    
    n = Len(v(UBound(v)))
    
    i = CLng(v(UBound(v)) + 1)
        
    v(UBound(v)) = Format(i, Left("000000000000", n))
    
    AddOne = Join(v, "-")

End Function

[Auxie]

Probably something like this.

Not too complex

Option Explicit
Sub demo()
    Dim s As String
    s = "ABD-DEF-00123"
    MsgBox s
    
    s = AddOne(s)
    MsgBox s
    
    s = AddOne(s)
    MsgBox s
    
    s = AddOne(s)
    MsgBox s
    
    s = AddOne(s)
    MsgBox s
    
End Sub

'assumes something like AAA-BBB-CCC-123 with numbers only in last position and seperated by hyphen
Function AddOne(s As String) As String
    Dim v As Variant
    Dim n As Long
    Dim i As String
    
    v = Split(s, "-")
    
    n = Len(v(UBound(v)))
    
    i = CLng(v(UBound(v)) + 1)
        
    v(UBound(v)) = Format(i, Left("000000000000", n))
    
    AddOne = Join(v, "-")

End Function

Don't think I understand this,

My reference will be following the below creitra,

ABC-DEF-00-GH-IJ-L-0001 (0001 is what i want to increase by 1)

Do i add the code into the body or in the UserForm_Initialize?

[mdmackillop]

Private Sub UserForm_Initialize()
Dim LR As Long
LR = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
TextBox1.Text = "ABC-DEF-00-GH-IJ-L-" & Format(LR, "0000")
End Sub

or

Private Sub UserForm_Initialize()
Dim LR As Long
LR = Sheets(1).[A1] = Sheets(1).[A1] + 1
TextBox1.Text = "ABC-DEF-00-GH-IJ-L-" & Format(LR, "0000")
End Sub

[Paul_Hossler]

Don't think I understand this,

My reference will be following the below creitra,

ABC-DEF-00-GH-IJ-L-0001 (0001 is what i want to increase by 1)

Do i add the code into the body or in the UserForm_Initialize?

Most likely in the UserForm_Initialize

It would probably be something like ...

Private Sub UserForm_Initialize() 
Worksheets("Sheet1").Range("A1").Value = AddOne(Worksheets("Sheet1").Range("A1").Value)
End Sub

So if A1 = "ABC-DEF-00-GH-IJ-L-0001" the AddOne will make it "ABC-DEF-00-GH-IJ-L-0002" each time the Initialize is called

Without having a sample workbook, it's hard to be specific.

For example, I'm not sure what you meant by "... Range("A:A")+1 ..."

[Auxie]

Hey,

I've added a copy of the excel file to this post. Currently working on the TQ -out page - very basic code and I'm probably missing out on loads of things, but I am a novice after all!


Thanks in advance

[mdmackillop]

So much clearer with a sample. After clearing your test data.

Private Sub UserForm_Initialize()
        Dim x
        x = Format(Cells(Rows.Count, 1).End(xlUp).Row - 2, "0000")
        TransmittalNo.SetFocus
        TransmittalNo.Value = "WOO-CAP-00-XX-RE-Z-" & x 'This is what i use atm and manually add the last 4 digits

A couple of points.
If this entry is not to be changed, consider
1) using a label instead of a textbox
2) disabling the textbox if you keep it
3) moving the focus to the next control to be entered

[Auxie]

So much clearer with a sample. After clearing your test data.

Private Sub UserForm_Initialize()
        Dim x
        x = Format(Cells(Rows.Count, 1).End(xlUp).Row - 2, "0000")
        TransmittalNo.SetFocus
        TransmittalNo.Value = "WOO-CAP-00-XX-RE-Z-" & x 'This is what i use atm and manually add the last 4 digits

A couple of points.
If this entry is not to be changed, consider
1) using a label instead of a textbox
2) disabling the textbox if you keep it
3) moving the focus to the next control to be entered

Excellent thank you - I had the focus at that position because that's where i had to originally input data, but thanks to you expertise I can now move it to another field! Thanks again!

If possible could a mod please remove my attachment in the above post? (#6)