Code numbering invoice

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,507
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
 
Something like following maybe:

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

Dave

it gives me error about ME
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
abdel. If you use my solution in post #2 you will get the full text in your userform
 
Upvote 0
it gives me error about ME

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

Code:
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
 
Last edited:
Upvote 0
Me is Keyword for your Userform & I assume that's where you have the code like following

Code:
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
 
Upvote 0
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.

Rich (BB code):
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
 
Last edited:
Upvote 0
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.

Code:
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.
 
Last edited:
Upvote 0
If you are using code as published, it will show the prefix.

Rich (BB code):
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
 
Upvote 0
i appreciate for you effort and i apology for my delaying honestly i don't note your post
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top