I have made some modifications to this code I found by McGimpsey.
Say the invoice number gets to a certain number, then I want to be able to change the 'series' say from 1000 to "A " & 0 so that the new numbers come out
A0001, A0002 etc.

The commented out if statement below is my best shot at it but it errors out on the line-- SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&

Code works okay without the if statement.

I'm thinking that the final goal would be to make the "A" a variable that can be changed on the sheet to indicate what department used a particular invoice number.

If I can get past the nNumber format issue, I believe I can work out the variable issue on the myself.

Thanks.
Howard



Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook.Sheets("Invoice")
Range("B2").ClearContents
End With
ActiveWorkbook.Save
End Sub

'McGimpsey and Associates
' Goes in ThisWorkbook module

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As String 'Long
Dim lr As Long
Dim DeptNme As String

lr = Cells(Rows.Count, 11).End(xlUp).Row

'InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
DeptNme = InputBox("Enter you Dept. Name.", "Department Name")
'Exit sub if Cancel button used or no text entered
If DeptNme = vbNullString Then Exit Sub

With ThisWorkbook.Sheets("Invoice")
With .Range("B1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With

With .Range("K1")
If IsEmpty(.Value) Then
.Value = "Used Invoice No.'s"
.Columns.AutoFit
End If
End With

With .Range("J1")
If IsEmpty(.Value) Then
.Value = "Department"
.Columns.AutoFit
End If
End With

With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"

'//****
' If nNumber = 5 Then
' nNumber = "A" & 0
' End If
'//****

.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&

.Copy Range("K" & lr).Offset(1, 0)
Range("J" & lr).Offset(1, 0).Value = DeptNme
End If
End With
End With

End Sub