Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Incrementing Invoice Number on Template (Excel 2000)

    Hello Excel Gurus,

    I am creating a template for an Invoice in Excel. I would like to have an invoice number that automatically increments every time the user creates a new invoice. Can someone help me?

    Cheers,

    Jocelyn

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Incrementing Invoice Number on Template (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Jocelyn

    OK when you say "every time the user creates a new invoice", how is this done?

    1) Does the User add a new worksheet and change it? If so each time you add the worksheet, you add 1to a number stored someplace, and you have it.
    2) Does the User empty an existing worksheet and add the changes, well same thing, each time the empty event takes place, an update event takes place and updates the number.

    Are you looking for a worksheet Formula solution, or a VBA solution?

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Invoice Number on Template (Excel 2000)

    Is this a real Excel template that has a .xlt extension? Exactly how does the user create the new invoice? How many users are there? If there are more than one user, are they sharing the template on a network?
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Invoice Number on Template (Excel 2000)

    Hi Wassim,
    Thanks for your response. To answer your questions:

    A user would create a new invoice by selecting New from the File menu, and then selecting the Invoice template.
    1) No they would use the same worksheet.
    2) No the user would add their details to the blank cells for the invoice - eg. date, customer details, invoice line items and costing details etc.

    I think a VBA solution is what I am looking for as I would think that the counter would need to be stored in the template - I am not sure though.

    Cheers and thanks,

    Jocelyn

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Invoice Number on Template (Excel 2000)

    Thanks for your response Legare,

    To answer your questions:
    Yes this is a real Excel template with an xlt. extension. The user would create the invoice by selecting New from the File menu and then selecting the appropriate template. There will only be one user.

    Thanks,

    Jocelyn

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Incrementing Invoice Number on Template (Excel 2000)

    See the thread starting at <post#=308198>post 308198</post#>, in particular the last reply by Steve (sdckapr).

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Invoice Number on Template (Excel 2000)

    This Message where Steve gives code that will do what you want.
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Invoice Number on Template (Excel 2000)

    Thanks kindly,

    This solution is excellent.

    Cheers,

    Jocelyn

  9. #9
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Invoice Number on Template (Excel 2000)

    Thanks kindly Legare,

    This solution is just what I need.

    Cheers,

    Jocelyn

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Invoice Number on Template (Excel 2000)

    I'd prefer code like this, which prevents trouble when two (or more) users try to update a number at the same time and thus cause a file-locking error:

    <pre>Public lCounter As Long

    Function GenerateNewNumber() As Boolean
    Dim lCountTimes As Long
    Dim sPath As String
    Dim sCounterFileName As String
    sPath = "h:YourPath"
    sCounterFileName = "FileCounter.txt"
    On Error Resume Next
    lCountTimes = 0
    Do
    lCountTimes = lCountTimes + 1
    Err.Clear
    Open sReferencePath & sCounterFileName For Input Lock Read Write As #1
    If Err.Number = 53 Then
    MsgBox "Counterfile not found", , sAppName
    GenerateNewNumber = False
    Exit Function
    End If
    If Err.Number = 70 Then
    Close #1
    Else
    Input #1, lCounter
    lCounter = lCounter + 1
    Close #1
    DoEvents
    Open sPath & sCounterFileName For Output Lock Read Write As #1
    Write #1, lCounter
    Close #1
    GenerateNewNumber = True
    End If
    Loop Until Err.Number = 0 Or lCountTimes = 100
    End Function
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Incrementing Invoice Number on Template (Excel 2000)

    I added a modification of your code and a modification of code from Chip Pearson and now the file will eliminate the macro code in the thisworkbook object. I didn't use your code a a function, since I wanted it all in once place to just delete the the one piece of code.

    The attached code codes in the thisworkbook object.
    Create a textfile to store the current number.
    In this example it is located/named:
    C:FileCounter.txt
    Change the cell and the format of the number as desired.

    For additional info see <post#=308260>post 308260</post#>

    Steve

    <pre>Option Explicit
    Private Sub Workbook_Open()
    'Code for preventing "File-locking error" with multiple people
    'opening the text file adapted from Jan Karel Pieterse code:
    'http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=309239&page=0
    '
    'Code for deleting the code adapted from Code found on Chip Pearson's site:
    'http://www.cpearson.com/excel/vbe.htm
    '----------------------------------------------------------------------------------
    Dim rCell As Range
    Dim sPath As String
    Dim sFilename As String
    Dim lCountTimes As Long
    Dim lCounter As Integer

    Set rCell = Worksheets("sheet1").Range("a1")

    sPath = "C:"
    sFilename = "FileCounter.txt"
    lCountTimes = 0

    If rCell.Value = "" Then
    On Error Resume Next
    Do
    lCountTimes = lCountTimes + 1
    Err.Clear
    Open sPath & sFilename For Input Lock Read Write As #1
    If Err.Number = 53 Then
    MsgBox "Counterfile not found "
    ActiveWorkbook.Close (False)
    End If
    If Err.Number = 70 Then
    Close #1
    Else
    Input #1, lCounter
    lCounter = lCounter + 1
    Close #1
    DoEvents
    Open sPath & sFilename For Output Lock Read Write As #1
    Write #1, lCounter
    Close #1
    rCell.Value = "IV-" & Format(lCounter, "0000") & _
    "-" & Format(Now, "mmyy")
    End If
    Loop Until Err.Number = 0 Or lCountTimes = 100
    If rCell.Value = "" Then
    MsgBox "Counterfile is in use try again later"
    ActiveWorkbook.Close (False)
    End If
    End If

    'Delete if new file
    If rCell.Value <> "" Then
    Dim VBC As CodeModule
    Dim lStartline As Long
    Dim lNumberLines As Long

    Set VBC = ThisWorkbook.VBProject.VBComponents("thisWorkbook" ).CodeModule

    With VBC
    lStartline = 1
    lNumberLines = .CountOfLines
    .DeleteLines lStartline, lNumberLines
    End With
    End If
    Set rCell = Nothing
    Set VBC = Nothing
    End Sub</pre>


Posting Permissions

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