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)
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
Advertisements