Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Sequential Numbers

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Thanks

    Carol

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Carol,

    Yes, but a bit more detail might be nice.



  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •