Forum Discussion

jpac1401's avatar
jpac1401
Copper Contributor
Nov 01, 2023

Increment relative cell (A2) in Excel, WITHOUT saving the file

Hi community, 


First of all I want to say thank you in advance for your time. My purpose is to try and create a new invoice number on cell A2, every time the excel file workbook is opened, and more specifically, WITHOUT saving the file. 

So far I have achieved my purpose with the following code below, this code fires only after I have to save the excel file. Is there a coding that would allow me to open the excel file and have cell A2 increment by value of 1 without having to save the file?

Again, thank you all in advance! 

excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 01, 2023

    jpac1401 

    I don't see any code.

    But if you want the number to increment without saving the workbook, you'll have to store the last value used elsewhere, for example in another workbook or in a text file.

    Without knowing what you currently have it's impossible to provide more detailed help.

    • jpac1401's avatar
      jpac1401
      Copper Contributor
      Nov 01, 2023

      Hi HansVogelaar, 

      Wow so sorry! I literally just slapped myself haha. Apologies here is the coding I have so far, that is not working: 

      Private Sub Workbook_Open()
      Dim hiddenWs As Worksheet
      Set hiddenWs = ThisWorkbook.Sheets("Hidden")
      Dim currentCount As Long
      currentCount = CLng(hiddenWs.Range("A2").Value)
      currentCount = currentCount + 1
      hiddenWs.Range("A2").Value = currentCount
      ThisWorkbook.Sheets("Sheet1").Range("A2").Value = Format(currentCount, "0000000")
      ThisWorkbook.Sheets("Sheet1").Range("P10").Value = Format(Date, "mm/dd/yyyy")
      End Sub


      Thank you in advance for your help and time! 

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 01, 2023

        jpac1401 

        That code will work if you save the workbook when you close it. If you don't save it, the value of Hidden!A2 will be the same as the previous time you opened the workbook.

        As an alternative, create a text file named InvoiceNo.txt and enter the last used value of the invoice number in it, then press Enter. Save and close the file.

        The code can then be:

        Private Sub Workbook_Open()
    Dim strFile As String
    Dim f As Integer
    Dim InvoiceString As String
    Dim invoiceNo As Long
    strFile = ThisWorkbook.Path & "\InvoiceNo.txt"
    f = FreeFile
    Open strFile For Input As #f
    Input #f, InvoiceString
    Close #f
    invoiceNo = CLng(InvoiceString) + 1
    Worksheets("Sheet1").Range("A2").Value = "'" & Format(invoiceNo, "0000000")
    f = FreeFile
    Open strFile For Output As #f
    Print #f, invoiceNo
    Close #f
End Sub

Resources