Code to Generate Sequential Number with a Prefix

Question

I have a small userform with textboxes and comboboxes in it. There is a summary table in worksheet. What i want is, when the userform is initialised, the code should go through the table column (voucher # i.e. 2) and generate the next number in the textbox2.

Here is my code:

Private Sub UserForm_Initialize()

Dim NextNum As Long, prefix As String
Dim i As Long

   prefix = "BPV/"
NextNum = Application.WorksheetFunction.Max(Sheet1.UsedRange.Columns(2))

i = NextNum + 1

Me.TextBox2.Enabled = False
Me.TextBox2.Value = prefix & i

End Sub

When the userform runs, it doesnt calculate the next number in sequence. Kindly review and help me.

Thanks

Are you sure the initialize method is running? The code you show is not valid because of the extra comma at the end of the first Dim statement. — Steven Kushubar
– Steven Kushubar, Commented Apr 25, 2016 at 12:09
yes it is. it only creates the number 1 after that it still showing the same number... — Salman Khan
– Salman Khan, Commented Apr 25, 2016 at 12:24
I'm not an expert, but if you place your code under Private Sub UserForm_Click() it works ok (when clicked). Don't know why is not working under Inicialize. — CMArg
– CMArg, Commented Apr 25, 2016 at 12:27
Actually i want to run the code when the userform initializes... — Salman Khan
– Salman Khan, Commented Apr 25, 2016 at 12:28

Ralph · Accepted Answer · 2016-04-25 13:15:13Z

3

The easiest way to generate a sequential number by row would be using the Excel function =row() and then format the number with the following NumberFormat:

"BP\//"000000

Selection.Numberformat = """BP\//""000000"

edited Apr 25, 2016 at 13:15

answered Apr 25, 2016 at 12:47

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jcarroll · Accepted Answer · 2016-04-25 12:35:26Z

0

One issue you might be having is that the BPV is preventing Excel from determining the max. Try something like the below for getting the current max.

Sub test()

    Dim rng As Range

    Dim fr As Integer
    Dim lr As Integer
    Dim i As Integer
    Dim curr As Integer
    Dim max As Integer

    Set rng = Range("A:A")

    fr = Worksheets("sheet1").Columns(2).Find(What:="*", after:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    lr = Worksheets("sheet1").Columns(2).Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row


    For i = fr To lr
        If Cells(i, 2) <> "" Then
            curr = Mid(Cells(i, 2), 4)
            If curr > max Then max = curr
        End If
    Next i

    MsgBox (max)

End Sub

answered Apr 25, 2016 at 12:35

jcarroll

5772 silver badges15 bronze badges

1 Comment

jcarroll Over a year ago

No it doesn't, I'm not writing you're entire code for you.

CMArg · Accepted Answer · 2016-04-26 21:14:57Z

0

I would need to know how you're doing this. But suppose that there is a button (Button1) in the worksheet that is calling the UserForm. The following code is doing something similar to what you want to achieve (I guess...).

In the module:

Sub Button1_Click()
    UserForm1.Show
End Sub

In the Userform:

Dim NextNum As Long, prefix As String
Dim i As Long

Private Sub UserForm_Initialize()
    prefix = "BPV/"
    NextNum = Application.WorksheetFunction.Max(Worksheets("Sheet1").Columns(2))
    i = NextNum + 1

    Me.TextBox2.Enabled = False
    Me.TextBox2.Value = prefix & i
    Worksheets("Sheet1").Cells(i, 2) = i
End Sub

answered Apr 26, 2016 at 21:14

CMArg

1,5973 gold badges14 silver badges32 bronze badges

Collectives™ on Stack Overflow

Code to Generate Sequential Number with a Prefix

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related