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

 

[abdelfattah]

Something like following maybe:

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

Dave

it gives me error about ME

 

[jmacleary]

abdel. If you use my solution in post #2 you will get the full text in your userform

 

[dmt32]

it gives me error about ME

Me is Keyword for your Userform & I assume that's where you have the code like following

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

Note use of the Range.Text property - This returns what you see in the Cell.

Dave

 

[abdelfattah]

Me is Keyword for your Userform & I assume that's where you have the code like following

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

Note use of the Range.Text property - This returns what you see in the Cell.

Dave

now this worked but i have the problem it shows me the next number is 2 not bsjd-002

 

[dmt32]

now this worked but i have the problem it shows me the next number is 2 not bsjd-002

If you are using code as published, it will show the prefix.

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

The Range.Text property shown in RED will return what you see in the cell. If you have modified code & used Range.Value then you need to return it back to Text.

If you are still having issues, then publishing all your forms code would be helpful


Dave

 

[jmacleary]

now this worked but i have the problem it shows me the next number is 2 not bsjd-002

Abdel, this is because you are using Dave's solution, which relies on formatting of the cell to show you the prefix. A combined version of my solution in post #2 and Dave's will work.

Private Sub UserForm_Initialize()
Dim Prefixtext As String
Prefixtext = "bsjd-"
    With Range("B9")
.Value = Prefixtext + Format(Mid(.Value, Len(Prefixtext) + 1) + 1, "000")        
Me.TextBox3.Text = .Text
    End With
End Sub

You will need to decide which way to go - my solution sets cell F9 to the correct text value automatically, Dave's relies on formatting. Either way works.

 

[abdelfattah]

If you are using code as published, it will show the prefix.

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

The Range.Text property shown in RED will return what you see in the cell. If you have modified code & used Range.Value then you need to return it back to Text.

If you are still having issues, then publishing all your forms code would be helpful


Dave

yes now it works thank you so mauch and jmacleary

 

[dmt32]

yes now it works thank you so mauch and jmacleary

You are welcome - glad that we both could help you

Dave

 

[abdelfattah]

i appreciate for you effort and i apology for my delaying honestly i don't note your post

 

[abdelfattah]

Abdel, this is because you are using Dave's solution, which relies on formatting of the cell to show you the prefix. A combined version of my solution in post #2 and Dave's will work.

Private Sub UserForm_Initialize()
Dim Prefixtext As String
Prefixtext = "bsjd-"
    With Range("B9")
.Value = Prefixtext + Format(Mid(.Value, Len(Prefixtext) + 1) + 1, "000")        
Me.TextBox3.Text = .Text
    End With
End Sub

You will need to decide which way to go - my solution sets cell F9 to the correct text value automatically, Dave's relies on formatting. Either way works.

thanks for your efforts