Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Numbering forms (Excel 2002)

    I'm looking for ideas on how we can create a running number (like invoice number) each time a template is used to create a new workbook. Each workbook needs to have a unique number, similar to invoices or purchase orders that need numbers for tracking. Ideally we could set the starting number. The template will be used only on one computer at a time. We would save the workbooks on the one computer, but at some point the workbooks would be moved to a file server or converted to PDF files or emailed to others, so the number would not change when moved.

    We are open for any solutions. We need that unique number and cannot rely on staff to remember to put the number on the file manually.

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

    Re: Numbering forms (Excel 2002)

    See the thread starting at <post:=308,872>post 308,872</post:>. (The solutions suggested near the end of the thread use a text file to store the invoice number. If you move the template to another computer, you'd need to move the text file with it.)

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

    Re: Numbering forms (Excel 2002)

    The code below, placed in the workbook open event routine in the module behind the ThisWorkbook object will place a sequential number in cell A1 of worksheet Sheet1. You will need to modify the line that reads strSeqFile = "C:WorkInvoiceSequence.txt" to specify a valid directory on the machine where the workbook is used. You set the starting number by changing the 0 in the line that reads lInvSeq = 0 to one less that the starting number and deleting the file specified in the previous sentence.

    <code>
    Private Sub Workbook_Open()
    Dim iFileNum As Integer, strSeqFile As String, lInvSeq As Long
    lInvSeq = 0
    strSeqFile = "C:WorkInvoiceSequence.txt"
    iFileNum = FreeFile()
    If Dir(strSeqFile) <> "" Then
    Open strSeqFile For Input As #iFileNum
    Input #iFileNum, lInvSeq
    Close #iFileNum
    End If
    lInvSeq = lInvSeq + 1
    Open strSeqFile For Output As #iFileNum
    Write #iFileNum, lInvSeq
    Close #iFileNum
    Application.EnableEvents = False
    Worksheets("Sheet1").Range("A1").Value = lInvSeq
    Application.EnableEvents = True
    End Sub
    </code>
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Numbering forms (Excel 2002)

    Thanks, Hans.

    I copied the code from the end of the thread for post 308,872, pasted in MS VBAProject This Workbook, Workbook Open, changed the Worksheets to "AnalyticalCOC" and changed the range to "al4."

    I saved the document as a template. I saved a text file as 'FileCounter.txt" on my C: drive. I closed the template and then tried to launch a file based on the template by double-clicking the template from Windows Explorer. I did not get an number in cell al4.

    What simple little thing did I do wrong?

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

    Re: Numbering forms (Excel 2002)

    1) Did you put a starting number in FileCounter.txt ?
    2) Did you set a reference (using Tools | References... in the Visual Basic Editor) to the Microsoft Visual Basic for Applications Extensibility 5.3 library?
    3) Did you make sure that macro security is not set to High, and that you enabled macros if prompted?
    4) Are you sure you looked at cell AL4 (that's what you specified)?

  6. #6
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Numbering forms (Excel 2002)

    I changed the VBA to rem out the Delete sub, then the macro worked as expected.

    The only issue with the macro is the it puts the number in the template when I open it to edit, which increments the number in the text file. I think we can live with that.

    Thanks, Hans

  7. #7
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Numbering forms (Excel 2002)

    I changed the SeqFile name, changed the Worksheets name and cell location, but could not get a number to display. Is there something else that I would need to customize?

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

    Re: Numbering forms (Excel 2002)

    If you have macro security set to Medium, you'll be prompted to enable macros when opening the template for editing. Clicking No willl prevent the code from running automatically.

  9. #9
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Numbering forms (Excel 2002)

    I did Enable the macro, but nothing happened with the short macro from Lagare Coleman.

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

    Re: Numbering forms (Excel 2002)

    My reply was to your <post:=563,150>post 563,150</post:>, not to <post:=563,156>post 563,156</post:>.

  11. #11
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Numbering forms (Excel 2002)

    Sorry, Hans, my screen was not displaying the threads.

    With the Delete code active, the macro seems to be deleting before running.

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

    Re: Numbering forms (Excel 2002)

    Legare's macro contains non-breaking spaces which may cause problems when you paste it into the Visual Basic Editor. The following version (with the correct names substituted) works fine for me:

    Private Sub Workbook_Open()
    Dim iFileNum As Integer, strSeqFile As String, lInvSeq As Long
    lInvSeq = 0
    strSeqFile = "C:FileCounter.txt"
    iFileNum = FreeFile
    If Dir(strSeqFile) <> "" Then
    Open strSeqFile For Input As #iFileNum
    Input #iFileNum, lInvSeq
    Close #iFileNum
    End If
    lInvSeq = lInvSeq + 1
    Open strSeqFile For Output As #iFileNum
    Write #iFileNum, lInvSeq
    Close #iFileNum
    Application.EnableEvents = False
    Worksheets("Sheet1").Range("A1").Value = lInvSeq
    Application.EnableEvents = True
    End Sub

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

    Re: Numbering forms (Excel 2002)

    Make sure that you clear the target cell (AL4) in the template before saving it.

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

    Re: Numbering forms (Excel 2002)

    It worked for me. Could you upload a workbook that shows what you did?
    Legare Coleman

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

    Re: Numbering forms (Excel 2002)

    Interesting. I copy that code from my message and paste it into XL2k and it works perfectly.
    Legare Coleman

Page 1 of 3 123 LastLast

Posting Permissions

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