Page 1 of 4 123 ... LastLast
Results 1 to 15 of 49
  1. #1
    Lounger
    Join Date
    Nov 2013
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Autoincrement a single cell on a template

    Hi all. I have a template designed as a packing slip. I would like to know how to make the "packing slip number" cell update by 1 everytime it is opened. I do not want the template saved or saved as just print it and close. The next person uses it and I need to see blank template with the next generated number. All help is greatly appreciated.

    Thanks all,

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,188
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You must save the number somewhere so the template will need to update another spreadsheet to get the latest number, or use a database.
    What happens if 2 people open the template but one takes ages to save the incremented number?

    cheers, Paul

  3. #3
    Lounger
    Join Date
    Nov 2013
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    test

  4. #4
    Lounger
    Join Date
    Nov 2013
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    This will be loaded on one PC with multiple users. I do not want the template saved, users can save as xlsx if they want or just print it. When they close the template, the next time it is opened, I would like to see the next number. It will be part of the process to save, print then close template.
    Thanks
    Mike

    Thanks all,

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maddmikk,

    The way I would approach this is to.
    1. Create a text file with the number 0 in it {or what ever number your last invoice was printed with}.
    2. Create an Auto_Open() macro in the template that reads the text file, increments the value by 1 then places the value in the appropriate cell.
    3. Create a Print button that will both print and then write the new value back to the text file. This way if the invoice isn't printed the number isn't incremented.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    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
    You could have a text file hold the current "packing slip number" and have the file read it, increment it, and store teh new number, without having to change the template. See for example the thread at http://windowssecrets.com/forums/sho...r-(Excel-2000)

    Steve

  7. #7
    Lounger
    Join Date
    Nov 2013
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks guy's. I'll start playing. I'm not an excell guy, just basics. I'm the network guy looking for an answer if I can do this the way it is required.

  8. #8
    Lounger
    Join Date
    Nov 2013
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Create an Auto_Open() macro in the template that reads the text file, increments the value by 1 then places the value in the appropriate cell

    I don't know how to create the macro

    Create a Print button that will both print and then write the new value back to the text file. This way if the invoice isn't printed the number isn't incremented.

    Again, sorry. I am not a macro guy either

  9. #9
    Lounger
    Join Date
    Nov 2013
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    sdckapr Will this method be valid in 2010 version?

  10. #10
    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
    Will this method be valid in 2010 version?
    I don't see why it would not...

    Steve

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    A much easier way is to have A cell (not being used) as the holder for the next Packing slip number with the font colored white so it is not visible. A one line macro in the workbook open routine will copy the value to the Packing slip number slot when the book opens and a routine in the workbook_beforeclose subroutine, clears the sections, increments the hidden placeholer cell by one, and then saves.
    Code:
    Private Sub Workbook_Open()
    'WHERE A1 IS THE PACKING SLIP SLOT AND J1 IS UNUSED CELL WITHIN PRINT AREA
    [A1] = [j1]
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'WHERE B1 IS THE RECEIVER FIELD
    If [b1] <> "" Then 'INDICATES THAT THE PACKING SLIP NUMBER WAS APPLIED
        'CODE TO CLEAR SHEET GOES HERE
        [j1] = [j1] + 1
        [A1] = ""
    End If
    End Sub
    There will be no need to maintain a second file and the coding would be far less

    HTH,
    Maud

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maddmikk,

    Here's code which can be used in a .xltm (2010 Macro Enabled Template).
    In a standard module
    Code:
    Option Explicit
    
       Const zFilePath  As String = "G:\BEKDocs\Excel\Test\" '<- Your path to file below here
       Const zFileName  As String = "LastInvoiceNo.txt"      '<- Your filename here
    
    
    Sub Auto_Open()
    
       Dim lLastInvNo As Long
    
       Open zFilePath & zFileName For Input Access Read As #1
       Input #1, lLastInvNo
       Close #1
    
       Range("InvoiceNo").Value = lLastInvNo + 1
       
    End Sub
    
    Sub Print_Invoice()
    
       Dim lLastInvNo As Long
    
       ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
    
       Open zFilePath & zFileName For Output As #1
       Write #1, Range("InvoiceNo").Value
       Close #1
    
    End Sub
    Maud, I think the OP wants a template because they don't want to save all the invoice info which will change each time as I read it.

    Notes:
    1. The Invoice Number cell must be named InoviceNo (Code uses this named range)
    2. You need to change the path / file name information for the text file that maintains the invoice number at the top of the code where indacated.
    3. You need to place a button on your Invoice for printing and have it execute the Print_Invoice code

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    ..you working for Apple now???? I-Novice-NO???

    zeddy

  14. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Right On....iNovice yep that's who uses Apples!

    But you'll notice the NO at the end so NO I don't work for the fanboy company.

    Sorry I just couldn't resist.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #15
    Lounger
    Join Date
    Nov 2013
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    RetiredGeek: Thank you that is what I want it to do. I will attempt first thing in the morning and advise.

Page 1 of 4 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
  •