Sequential Numbers

[slimpickens]

Is there anyway I can have a textbox register the next available number after each transaction is updated.

Thanks

Carol

[Mark O'Brien]

Carol,

Yes, but a bit more detail might be nice.

[slimpickens]

Mark,
In actual fact the reference number should be six digits long but the first five numbers should be 35001 while the last digit starts with 0. Every transaction after that should have 35001 and the next number until you reach 9. The next transaction after 350019 should change the 35001 to 35002 and start the sixth number with 0 again. I seem to be reaching for the stars here since my knowledge of Vb is limited, but I thought maybe someone could help me.

Carol

[Tom Schreiner]

Hi Carol.
The concept of what you need to accomplish seems easy enough, but to encourage people on this board to reply with working examples, please give as much detail as you are willing to give...

Ex.

I have a textbox(named) on sheet(named) which tracks numbers in Column(Label) beginning at row(Number).

Just an example.
We have not a clue as to where your data is coming from or going to????

Thanks,
Tom


[ This Message was edited by: TsTom on 2002-04-09 10:25 ]

[slimpickens]

My apologies,

I have a textbox(refnumber) on my Userform claim) in a wrksheet(Payments). I have made up a list of numbers in a next wrksheet(numbers) which I have in Column A beginning at row(2). There are other controls which would make up a claim transact"ion so that when I click on my "Add Record button all the data would be sent to the wrksheet(Payments). When my message box asks "do you want to add another record", I want the form to return with the next available number registered in textbox (refnumber) or if the user closes the form and reopens it, I want the next available number to be registered.

Hope this is enough info.

Carol

[Jay Petrulis]

On 2002-04-09 10:50, slimpickens wrote:
My apologies,

I have a textbox(refnumber) on my Userform claim) in a wrksheet(Payments). I have made up a list of numbers in a next wrksheet(numbers) which I have in Column A beginning at row(2). There are other controls which would make up a claim transact"ion so that when I click on my "Add Record button all the data would be sent to the wrksheet(Payments). When my message box asks "do you want to add another record", I want the form to return with the next available number registered in textbox (refnumber) or if the user closes the form and reopens it, I want the next available number to be registered.

Hope this is enough info.

Carol

Hi Carol,

In your UserForm initialize event, search the column of numbers for the maximum and add 1.

--------------------
Private Sub UserForm_Initialize()
Dim maxval
maxval = WorksheetFunction.Max(Sheets("Sheet1").Range("A:A")) + 1
UserForm1.TextBox1.Text = maxval
End Sub
-------------------

I am unsure of all the sheet/range references, so the code above only uses a generic Sheet1, Column A. Please adjust to fit your actual worksheet.

HTH,
Jay