Code numbering invoice

[abdelfattah]

hi, expert
i have userform connect with sheet i would show the next number in textbox3
the textbox3 connected with range in my sheet f9 i know this code range("f9").value=range("f9").value+1
but my numbering is coplicated contains letters and numbers and symbols here my numbring is bsjd-001
i hope help me

 

[jmacleary]

Hello. If your numbering always ends with the number you want to add to, then this will do it. Replace the bsjd- with another text if necessary.

Dim Prefixtext As String
Prefixtext = "bsjd-"
Range("f9").Value = Prefixtext + Format(Mid(Range("F9").Value, Len(Prefixtext) + 1) + 1, "000")

This will always add 1 to the number at the end and keep it as 3 digits (i.e. 002,003 etc) (and when it gets to 999 it will carry on with 1000, 1001, etc.. If there may be text after the number, then a more complicated solution is needed.

 

[abdelfattah]

Hello. If your numbering always ends with the number you want to add to, then this will do it. Replace the bsjd- with another text if necessary.

Dim Prefixtext As String
Prefixtext = "bsjd-"
Range("f9").Value = Prefixtext + Format(Mid(Range("F9").Value, Len(Prefixtext) + 1) + 1, "000")

This will always add 1 to the number at the end and keep it as 3 digits (i.e. 002,003 etc) (and when it gets to 999 it will carry on with 1000, 1001, etc.. If there may be text after the number, then a more complicated solution is needed.

thanks so much the code is worked but i would show the next number in textbox3 in userform directly when i run userform

 

[dmt32]

Hi,
You use the Range.NumberFormat property to do what you want

With Range("B9")
        .NumberFormat = """bsjd-""000"
        .Value = .Value + 1
    End With

Although you will see your text prefix in the cell, it's underlying value is just a number which you can increment in manner required

Note: ensure when you first run the code that you only have a number (no prefix) in the cell. Code will add this for you.

Dave

 

[abdelfattah]

Hi,
You use the Range.NumberFormat property to do what you want

With Range("B9")
        .NumberFormat = """bsjd-""000"
        .Value = .Value + 1
    End With

Although you will see your text prefix in the cell, it's underlying value is just a number which you can increment in manner required

Note: ensure when you first run the code that you only have a number (no prefix) in the cell. Code will add this for you.

Dave

thanks for your replying but your code give me error run time13
the error line is: .Value = .Value + 1

 

[dmt32]

thanks for your replying but your code give me error run time13
the error line is: .Value = .Value + 1

Did you follow the note I added when first using the code?

Note: ensure when you first run the code ensure that you only have a number (no prefix) in the cell. Code will add this for you.

Dave

 

[abdelfattah]

Did you follow the note I added when first using the code?



Dave

sorry i don't follow your note now the code works perfectly but there is one thing i would show the next number in texrbox3 in userform when i run userform directly

 

[jmacleary]

abdel. Where are you putting the code we have provided (either solution works, mine fills the f9 cell with the full text, Dave's with just the number? You would need to refresh the data in the userform itself once the field has been updated.

 

[dmt32]

sorry i don't follow your note now the code works perfectly but there is one thing i would show the next number in texrbox3 in userform when i run userform directly

Something like following maybe:

  With Range("B9")
        .NumberFormat = """bsjd-""000"
        .Value = .Value + 1
        Me.TextBox3.Text = .Text
    End With

Dave

 

[abdelfattah]

abdel. Where are you putting the code we have provided (either solution works, mine fills the f9 cell with the full text, Dave's with just the number? You would need to refresh the data in the userform itself once the field has been updated.

i put the code in

Private Sub UserForm_Initialize()
TextBox3.Text = Range("F9").Value

end sub

then show me the numbering invoice is 2 not
[TABLE="width: 255"]
<tbody>[TR]
[TD="class: xl66, width: 255, colspan: 2"]bsjd-002
is there any way to sole this problem

[/TD]
[/TR]
</tbody>[/TABLE]