Can I create a sequential numbering system on an Excel document?

[Jamie]

I have been asked to find a way that our expense sheet (in Excel) can create
a numbering system where a new number can be created everytime it is opened
so that accounting can log the number and know that it is a new or previously
submitted expense report by the number at the top of the document.

Is this possible?

 

[Peo Sjoblom]

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

--


Regards,


Peo Sjoblom

 

[FSt1]

hi
see this site for ideas
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

regards
FSt1

 

[Pete_UK]

Have a look here:

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

Hope this helps.

Pete

 

[Jamie]

Thanks! I don't know anything about VB, but I did follow the directions, can
you help me debug it? This is what I have put in ThisWorkbook; it keeps
pointing to "With ThisWorkbook.Sheets(OverheadExp)" and giving me a runtime
error 9. Subscript out of range. I am not sure what I am doing wrong. Thanks
for any help!

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "OverheadExp"
Const sKEY As String = "OverheadExp_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets(OverheadExp)
With .Range("B1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

 

[Gord Dibben]

Jamie

This line assigns sSECTION as the string "OverheadExp"
Const sSECTION As String = "OverheadExp"

So you must use that further down
With ThisWorkbook.Sheets(sSECTION)


Gord Dibben MS Excel MVP

 

[Jamie]

Thanks for the information. I still can't get it to work though.

I have saved - With ThisWorkbook.Sheets(OverheadExp) should it be somewhere
else also?