Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Assigning unique numbers (97/xp)

    Could any please help me with this: I am creating a invoice with running numbers. It needs to be unique each time and stored somewhere. This is a template. How do i make the number assigned remain unique and never to be used again.

    Ive created a simple macro to increment by one, but only from the state the current cell stands at. e.g if cell content is 4, the macro will add one more. It can be easily modified by changing the content.

    Please help

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning unique numbers (97/xp)

    It sounds like you're worried someone will change the numbering sequence. How much security do you need around this?

    You could write the invoice number out to a text file so that when Excel starts, it can read the last number used. This is not entirely secure since the text file is susceptible to corruption, but at least it's separate from the spreadsheet.

    This actually sounds like a job for Access where you can create a form and make the invoice number an indexed field which guarantees it won't be repeated.

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning unique numbers (97/xp)

    Don't feel like I have enuf knowledge in dbase to do this, but yes I agree. I'm working on that however, excel is where its at, at the mo. Please explain regarding text file ...? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I am the only person generating the invoices, but I don't want to assign the same invoice number to two different people

  4. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning unique numbers (97/xp)

    Krista,
    I was in a similar "unique number" situation. Chip's concern for security is certainly valid, especially for invoices. I had a separate spreadsheet (no longer available to me) that was simply a one-up column of numbers. I used a macro in the spreadsheet for the following steps:

    1. UNPROTECT the spreadsheet with a password. (You need to know either how to start the macro or the password, or you can't mess with the spreadsheet.)
    2. Locate the maximum value of the column & put that value into a variable n
    3. Increment one more row, post the next number (n+1)
    4. PROTECT the spreadsheet again.
    5. Return the "serial number" in a small form.

    Depending on how good you are with VBA, you could have a few forms to query the user for invoice info (Customer name, say) and post that as a minor help along with the serial number, or just make the form increment the numbers for you to look at. Not as smooth as Access' AutoNumber, but it worked for me.

    If you need more help than this, just post back here.

    Errol

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning unique numbers (97/xp)

    Sorry if I get too basic on you. I'm not sure of your knowledge level and I want to try to solve this in this post.

    If you're the only one working with this, then an easier answer may be to store the last invoice number in a named cell on a protected hidden sheet.

    1. Insert Worksheet - I'll assume this sheet is named Sheet2
    2. Insert Name Define - Give a cell a name like LastInvoice
    3. Tools Protection Protect Sheet - You probably don't need a password. Just leave it blank.
    4. Format Sheet Hide

    Now that cell named LastInvoice is fairly difficult to corrupt.

    Let's say that your invoice sheet has the invoice number in a cell named InvoiceNum. Go to that cell and type =LastInvoice+1. You can protect InvoiceNum so that it's a little more difficult to corrupt. All cells are normally locked, but can still be changed because Worksheet Protection is normally disabled. Select all cells in the worksheet by clicking on the box above row 1 and to the left of column A. Then do Format Cells and under the Protection tab, uncheck Locked and click OK. Then select the InvoiceNum cell and do Format Cells and under the Protection tab, make sure Locked is checked. Click OK. At this point, InvoiceNum is the only locked cell on the sheet. Then do Tools Protection Protect Sheet to enable Worksheet Protection.

    When your invoice is complete, you can run a macro that saves the last number and saves your current xls file so that the value of LastInvoice is saved:

    <pre>Sub InvoiceComplete()
    Application.Calculate
    Sheets("Sheet2").Unprotect
    Range("LastInvoice")=Range("InvoiceNum").Value
    Sheets("Sheet2").Protect
    ActiveWorkbook.Save
    End Sub
    </pre>

    You can even make this macro print your invoice and clear the template so that it's ready for new info. It would also seem to make sense to store your invoice information in a database so that you can track their status.

    Hope that helps.

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

    Re: Assigning unique numbers (97/xp)

    You say that "This is a template." Does that mean that this is actually a file with a .xlt extension? If so, you will need to keep the invoice number in a separate file and put code in the template's Workbook Open event routine to read this file, store the invoice number in the invoice, increment the number and write it back to the separate file. It also sounds like this template is shared among several users on a network. Is that correct? If this is not a .xlt file, and/or it is not being shared, please give a more detailed description of how you are doing this. Depending on the answers to these questions, some of the other methods described in other responses may not work.
    Legare Coleman

  7. #7
    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: Assigning unique numbers (97/xp)

    <P ID="edit" class=small>(Edited by sdckapr on 04-Nov-03 09:47. Added a link to some additional code in another post)</P>Here is a relatively simple scheme.
    Create a textfile to store the current number.
    In this example it is located/named:
    C:InvoiceNumber.txt

    Then add this workbook open macro to this workbook object of your invoicetemplate:
    <pre>Option Explicit
    Private Sub Workbook_Open()
    Dim rCell As Range
    Dim sFilename As String
    Dim iInvoiceNum As Integer
    Dim sInvoiceNum As String

    Set rCell = Worksheets("sheet1").Range("a1")
    sFilename = "C:InvoiceNumber.txt"

    If rCell.Value = "" Then
    Open sFilename For Input As #1
    Line Input #1, sInvoiceNum
    Close #1
    iInvoiceNum = Val(sInvoiceNum) + 1

    Open sFilename For Output As #1
    Write #1, iInvoiceNum
    Close #1

    rCell.Value = "IV-" & Format(iInvoiceNum, "0000") & _
    "-" & Format(Now, "mmyy")
    End If
    Set rCell = Nothing
    End Sub</pre>


    Save the file as a template in the office template folder.

    Now when you do a file - New
    You can choose this template, and the file will create a new xls file and place in Sheet1!A1 the invoice number in the format:
    IV-####-mmyy
    That is an "IV-" a four-digit number + "-" and the date in mmyy format.

    Change as appropriate for the :
    Text file to open
    the cell for the invoice number
    and the format for the unique number as desired. You can add day/time also if desired or remove the date altogether or add more digits.

    During this process it opens the text file, reads the current number, increments it, then writes it to the text file.

    The file created does contain a workbook open event, though it doesn't do anything if the cell with the invoice number is blank whihc is what we did. Its annoyance is that you will get the macro warning when it is opened later.

    Steve
    See <post#=309329 >post 309329 </post#> for enhancements to this code.

Posting Permissions

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