Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: How to add automatic tracking number to a form

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

    Default

    Hello,

    I'm trying to create an invoice form, and I want to assign automatic tracking number to the form every time it's filled up. How should I do it?
    Could anybody help?

    Vitas

  2. #2
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    MS should add this function to Excel, I bet someone asks for this code two or three times a week on this board. If I was sure the old board was added to the new board I would tell you just search for the code or function you like best, but since this is the new board, Try this code:

    Private Sub Workbook_Open()
    'By Joe Was, 2/2002.
    'Place this code in the "ThisWorkbook" Module
    'to increment on the opening of the workbook.

    'Sub Worksheet_Activate()
    'Use the "Activate" Sub in a Sheet Module
    'to increment on a sheet selection.

    [A3].Value = [A3] + 1
    ActiveWorkbook.Save
    MsgBox "Each time this sheet is opened" & Chr(13) & "the starting number is increased." _
    & Chr(13) & "And this blank template is saved" & Chr(13) & _
    "with the new starting number!" & Chr(13) & Chr(13) & _
    "Please, save any added data" & Chr(13) & "with a new file Name!" & Chr(13) & Chr(13) & _
    "To preserve system integrity" & Chr(13) & "follow these directions!"
    End Sub

    Hope this helps. JSW


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

    Default

    It works! Thanks a lot.

    Vitas

  4. #4
    New Member
    Join Date
    Mar 2006
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding automatic tracking number to a form

    Ok, I found your explanation for this, but am slightly confused. Were not sure where youre supposed to put the code and Im not sure I follow the rest of it either. Is there any way that you could possibly explain it in a version for not so literate computer people?

  5. #5
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Private Sub Workbook_Open()
    'Place this code in the "ThisWorkbook" Module
    'to increment on the opening of the workbook.

    Sheets("Sheet1").Select

    [A3].Value = [A3] + 1

    ActiveWorkbook.Save

    MsgBox "Each time this sheet is opened" & Chr(13) & "the starting number is increased." _
    & Chr(13) & "And this blank template is saved" & Chr(13) & _
    "with the new starting number!" & Chr(13) & Chr(13) & _
    "Please, save any added data" & Chr(13) & "with a new file Name!" & Chr(13) & Chr(13) & _
    "To preserve system integrity" & Chr(13) & "follow these directions!"
    End Sub


    "Sheets("Sheet1")" in this the name of the sheet that gets the updated number. You may need to change this to your sheets name!

    Cell "A3" in the code above is the cell that gets the new number. You may need to change this to the cell address that gets this number.


    On the sheet Tab, Right click select View code. In the VBA Editor on the you should have a "Project Explorer" open if not Mouse over the ToolBar icons to display each icon's name, look for "Project Explorer"

    Double click the item in the Project Explorer called "ThisWorkbook"
    In the code window that opens Paste a copy of the above code.

    Click the upper most Right Close "x" to return to your sheet.
    JSW: Try and try again: "The way of the Coder!"

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
  •