How do I generate a sequential serial number every time I tab to a new line?

I am trying to create a chart which would auto serial number every time I start a new line.

Example,


000-001
000-002
000-003
etc etc.

I only want the serial number to appear when a new line is started, not before.


I tried a VBA code, which worked for a while but for some reason it has stopped.

Private Sub Workbook_Open()

Sheets(1).Range("A1").Value = Sheets(1).Range("A1").Value + 1
ActiveWorkbook.Save



End Sub

I could really use some help!

I am using excel 2007 at work. I know that Access would probably be better but unfortunately I am using a work computer and the have Excel.

depends but with
000-000 in a1
in a2
=IF(ISBLANK(B2),"",IF(RIGHT(A1,3)="999",TEXT(LEFT(A1,3)+1,"000")&"-000",LEFT(A1,4)&TEXT(RIGHT(A1,3)+1,"000")))
dragged down
will auto number as soon as something is entered in b2

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I have added them for you this time

This code might be what you want

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim MaxRow As Long
    Dim MaxNum As Long

    MaxRow = ActiveSheet.UsedRange.Rows.Count

    If Target.Row = MaxRow + 1 Then MaxNum = _
       Application.WorksheetFunction.Max(Range(Cells(1, 1), Cells(MaxRow, 1))) + 1
    Cells(Target.Row, 1) = MaxNum
End Sub

Copy the code
Select the worksheet in which you want the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

Does the code do what you want?