totn Excel

MS Excel 2003: Autonumber a cell each time the spreadsheet is opened

This Excel tutorial explains how to write a macro to autonumber a cell each time the spreadsheet is opened in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Microsoft 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 look at an example.

Download Excel spreadsheet (as demonstrated below)

Microsoft Excel

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