Help with turning off auto numbering once a excel file is saved

**mkeehn** · 09-06-2011, 04:01 PM

My company is now using Microsoft 2010. First let me say I have no experience in writing macros or VBA Project. I copied a code from a forum that worked to provide auto numbering to our Excel Travel & Expense form. I would like to save the Excel template/or spreadsheet on our common drive forcing users to open (to get the auto number) and save a copy each time to their H:\drive. Once the file is saved on the H:\ drive the auto number needs to be turned off. This would allow the user to continue editing the document if necessary and to avoid duplicate numbering. Can anyone help me write a code? Below is what I have to turn the auto numbering on. I also need to protect certian fields in the document from being type in.

Private Sub Workbook_Open()
With Range("J5")
.NumberFormat = "0000000"
.Value = .Value + 1
End With
End Sub

**JBeaucaire** · 09-06-2011, 07:18 PM

This is the technique I use. It presumes you are opening the template via shortcuts so that it never really opens the template, but a new workbook, each time. The workbook_open does the same thing yours does, with the exception you forgot to include the Sheet Name reference in yours. Best include that...

Also, I've designated cell AA1 as the "doublecheck" cell on my new workbook, and it will only increment the number if that cell is empty. The workbook_beforesave event not only increments the actual template, but it puts a flag in AA1 to make sure this workbook never does any of this activity again.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("Sheet1")
    If .Range("AA1") = "" Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.DisplayAlerts = False
        
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\Jerry\My Documents\Excel Tips\Invoice.xlt", Editable:=True
        .Range("B2").Value = .Range("B2").Value + 1
        ActiveWorkbook.Close True
    
        .Range("AA1") = "Incremented"
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
End With
End Sub

Private Sub WorkBook_Open()

    With Sheets("Sheet1")
        If .Range("AA1") = "" Then .Range("B2").Value = .Range("B2").Value + 1
    End With
        
End Sub

**JBeaucaire** · 09-06-2011, 07:22 PM

With multiple people accessing the same template, I realized you really might need to make the "numbering" part occur compltely during the SaveAs part. So here's the function without the Workbook_Open macro at all. When you do a SaveAs, it will secretly reopen the template, increment the CURRENT number by one, remember it, save the template, put that number into your current workbook, then continue with a normal SaveAs function.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim NewVal As Long

With Sheets("Sheet1")
    If .Range("AA1") = "" Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.DisplayAlerts = False
        
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\Jerry\My Documents\Excel Tips\Invoice.xlt", Editable:=True
        With ActiveWorkbook.Sheets("Sheet1")
            .Range("B2").Value = .Range("B2").Value + 1
            NewVal = .Range("B2").Value
        End With
        ActiveWorkbook.Close True
    
        .Range("B2").Value = NewVal
        .Range("AA1") = "Incremented"
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
End With

'normal save continues from here...
End Sub