PDA

View Full Version : [SOLVED] Userforms - Textbox Autofill



Auxie
06-16-2017, 05:17 AM
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
06-16-2017, 06:15 AM
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
06-19-2017, 07:58 AM
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
06-19-2017, 10:31 AM
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
06-19-2017, 11:00 AM
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
06-20-2017, 12:41 AM
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
06-20-2017, 01:38 AM
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
06-20-2017, 06:25 AM
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) :)