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?
--
Jamiej

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

--


Regards,


Peo Sjoblom

"Jamie" wrote in message
...
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?
--
Jamiej

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

regards
FSt1

"Jamie" wrote:

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?
--
Jamiej

Have a look he

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

Hope this helps.

Pete

On Nov 10, 5:01*pm, Jamie wrote:
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?
--
Jamiej

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

--
Jamiej


"FSt1" wrote:

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

regards
FSt1

"Jamie" wrote:

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?
--
Jamiej

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


On Mon, 10 Nov 2008 14:44:06 -0800, Jamie
wrote:

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

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?
--
Jamiej


"Gord Dibben" wrote:

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


On Mon, 10 Nov 2008 14:44:06 -0800, Jamie
wrote:

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