MS Excel: Autonumber a cell each time the spreadsheet is opened in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, I have a spreadsheet that I want to use that each time I open it, I want it to autonumber a specific cell. I want to start off using 0001 and when the spreadsheet is opened a second time, I want it to go to 0002. Is there a way to get this to occur?

Answer: There are several "events" available within an Excel spreadsheet where you can place VBA code. In your case, we want to check for a duplicate value when the "Workbook_Open" event fires.

Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In our spreadsheet, we've set up our auto-incrementing number in cell A2. When the spreadsheet is opened, the value in this cell will increase by 1.

You can press Alt-F11 to view the VBA code.

Macro Code:

The macro code looks like this:

Private Sub Workbook_Open()

    Dim LOldVal As Integer
    Dim LNewVal As String

    'Retrieve current number
    LOldVal = Sheets("Sheet1").Range("A2").Value
    LNewVal = Format(LOldVal + 1, "0000")

    'Update to new number
    Sheets("Sheet1").Range("A2").Value = "'" & LNewVal

End Sub