I need the formula or path to i guess to make a template and each time it is
open it generates a new number, Like a invoice number. For example. in cell
A1 Invoice #
in cell B1 we would have the number 1. then save it and then when you open
the template again the #2 would be in cell B1 automatically? can anyone help?

Only vba code can do this. Something like this will work
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 Long

With ThisWorkbook.ActiveSheet
With .Range("B1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "1")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

"Bcoleman75" wrote:

I need the formula or path to i guess to make a template and each time it is
open it generates a new number, Like a invoice number. For example. in cell
A1 Invoice #
in cell B1 we would have the number 1. then save it and then when you open
the template again the #2 would be in cell B1 automatically? can anyone help?

mike thank you but i am very very basic wth this vba codes etc. I don't know
where to begin, do I just copy and past what you have and it should work?

"Mike" wrote:

Only vba code can do this. Something like this will work
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 Long

With ThisWorkbook.ActiveSheet
With .Range("B1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "1")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

"Bcoleman75" wrote:

I need the formula or path to i guess to make a template and each time it is
open it generates a new number, Like a invoice number. For example. in cell
A1 Invoice #
in cell B1 we would have the number 1. then save it and then when you open
the template again the #2 would be in cell B1 automatically? can anyone help?

Is your template a true Template(*.XLT)

If not, open a new file and build your invoice layout on a sheet.

Enter this code in Thisworkbook module........to acces that module
right-click on the Excel Icon left of "File" and select "View Code"

Private Sub Workbook_Open()
if thisworkbook.path = "" then
'because the workbook is opened based upon a Template
'it's never been saved, so increment the number.
Sheet1.Range("B1").Value = Sheet1.Range("B1").Value + 1
end if
End Sub

Copy/paste the code into that module.

Alt + q to return to the Excel window.

FileSave AsFile TypeTemplate(*.xlt)


Gord Dibben MS Excel MVP

On Mon, 22 Dec 2008 15:31:01 -0800, Bcoleman75
wrote:

I need the formula or path to i guess to make a template and each time it is
open it generates a new number, Like a invoice number. For example. in cell
A1 Invoice #
in cell B1 we would have the number 1. then save it and then when you open
the template again the #2 would be in cell B1 automatically? can anyone help?

I could send you a workbook that is working. Or yes you can just copy and
paste the code into the ThisWorkBook Module.

"Bcoleman75" wrote:

mike thank you but i am very very basic wth this vba codes etc. I don't know
where to begin, do I just copy and past what you have and it should work?

"Mike" wrote:

Only vba code can do this. Something like this will work
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 Long

With ThisWorkbook.ActiveSheet
With .Range("B1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "1")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

"Bcoleman75" wrote:

I need the formula or path to i guess to make a template and each time it is
open it generates a new number, Like a invoice number. For example. in cell
A1 Invoice #
in cell B1 we would have the number 1. then save it and then when you open
the template again the #2 would be in cell B1 automatically? can anyone help?

if you have a work book and don't mind sharing that would be great, i've been
working on this for almost 6 wks and there is no software out there to
generate sequnital number system and i just need a code through vb i
guess....thanks for all your help!!!!

"Mike" wrote:

I could send you a workbook that is working. Or yes you can just copy and
paste the code into the ThisWorkBook Module.

"Bcoleman75" wrote:

mike thank you but i am very very basic wth this vba codes etc. I don't know
where to begin, do I just copy and past what you have and it should work?

"Mike" wrote:

Only vba code can do this. Something like this will work
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 Long

With ThisWorkbook.ActiveSheet
With .Range("B1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "1")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

"Bcoleman75" wrote:

I need the formula or path to i guess to make a template and each time it is
open it generates a new number, Like a invoice number. For example. in cell
A1 Invoice #
in cell B1 we would have the number 1. then save it and then when you open
the template again the #2 would be in cell B1 automatically? can anyone help?

Where do I send it to

"Bcoleman75" wrote:

if you have a work book and don't mind sharing that would be great, i've been
working on this for almost 6 wks and there is no software out there to
generate sequnital number system and i just need a code through vb i
guess....thanks for all your help!!!!

"Mike" wrote:

I could send you a workbook that is working. Or yes you can just copy and
paste the code into the ThisWorkBook Module.

"Bcoleman75" wrote:

mike thank you but i am very very basic wth this vba codes etc. I don't know
where to begin, do I just copy and past what you have and it should work?

"Mike" wrote:

Only vba code can do this. Something like this will work
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 Long

With ThisWorkbook.ActiveSheet
With .Range("B1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "1")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

"Bcoleman75" wrote:

I need the formula or path to i guess to make a template and each time it is
open it generates a new number, Like a invoice number. For example. in cell
A1 Invoice #
in cell B1 we would have the number 1. then save it and then when you open
the template again the #2 would be in cell B1 automatically? can anyone help?

sorry i should have sent email address



thks,

bryan

"Mike" wrote:

Only vba code can do this. Something like this will work
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 Long

With ThisWorkbook.ActiveSheet
With .Range("B1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "1")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

"Bcoleman75" wrote:

I need the formula or path to i guess to make a template and each time it is
open it generates a new number, Like a invoice number. For example. in cell
A1 Invoice #
in cell B1 we would have the number 1. then save it and then when you open
the template again the #2 would be in cell B1 automatically? can anyone help?

Or you could send you Sheet to

"Bcoleman75" wrote:

if you have a work book and don't mind sharing that would be great, i've been
working on this for almost 6 wks and there is no software out there to
generate sequnital number system and i just need a code through vb i
guess....thanks for all your help!!!!

"Mike" wrote:

I could send you a workbook that is working. Or yes you can just copy and
paste the code into the ThisWorkBook Module.

"Bcoleman75" wrote:

mike thank you but i am very very basic wth this vba codes etc. I don't know
where to begin, do I just copy and past what you have and it should work?

"Mike" wrote:

Only vba code can do this. Something like this will work
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 Long

With ThisWorkbook.ActiveSheet
With .Range("B1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "1")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

"Bcoleman75" wrote:

I need the formula or path to i guess to make a template and each time it is
open it generates a new number, Like a invoice number. For example. in cell
A1 Invoice #
in cell B1 we would have the number 1. then save it and then when you open
the template again the #2 would be in cell B1 automatically? can anyone help?

I woud send you the template but its at work and I'm trying to make a dummy
worksheet to just see what i can do then work on it more at my office. if you
have something you can email me that would be nice and I can look it over.
I'm needing a number system for audit purposes.



"Mike" wrote:

Or you could send you Sheet to

"Bcoleman75" wrote:

if you have a work book and don't mind sharing that would be great, i've been
working on this for almost 6 wks and there is no software out there to
generate sequnital number system and i just need a code through vb i
guess....thanks for all your help!!!!

"Mike" wrote:

I could send you a workbook that is working. Or yes you can just copy and
paste the code into the ThisWorkBook Module.

"Bcoleman75" wrote:

mike thank you but i am very very basic wth this vba codes etc. I don't know
where to begin, do I just copy and past what you have and it should work?

"Mike" wrote:

Only vba code can do this. Something like this will work
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 Long

With ThisWorkbook.ActiveSheet
With .Range("B1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "1")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

"Bcoleman75" wrote:

I need the formula or path to i guess to make a template and each time it is
open it generates a new number, Like a invoice number. For example. in cell
A1 Invoice #
in cell B1 we would have the number 1. then save it and then when you open
the template again the #2 would be in cell B1 automatically? can anyone help?

Gord,

Would that code work in a template (.xlt) file as well? I've tried various
methods in the past and had all but given up.
--
Riker1074


"Gord Dibben" wrote:

Is your template a true Template(*.XLT)

If not, open a new file and build your invoice layout on a sheet.

Enter this code in Thisworkbook module........to acces that module
right-click on the Excel Icon left of "File" and select "View Code"

Private Sub Workbook_Open()
if thisworkbook.path = "" then
'because the workbook is opened based upon a Template
'it's never been saved, so increment the number.
Sheet1.Range("B1").Value = Sheet1.Range("B1").Value + 1
end if
End Sub

Copy/paste the code into that module.

Alt + q to return to the Excel window.

FileSave AsFile TypeTemplate(*.xlt)


Gord Dibben MS Excel MVP

On Mon, 22 Dec 2008 15:31:01 -0800, Bcoleman75
wrote:

I need the formula or path to i guess to make a template and each time it is
open it generates a new number, Like a invoice number. For example. in cell
A1 Invoice #
in cell B1 we would have the number 1. then save it and then when you open
the template again the #2 would be in cell B1 automatically? can anyone help?

Actually the code I provided won't work properly in the Template.

I sort of misread your post<g

The code just prevents the number from updating in the workbook created from
the template when next opened.

You need something like code from John McGimpsey's site to first increment
the number from the template.

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


Gord

On Fri, 26 Dec 2008 08:53:01 -0800, Riker1074
wrote:

Gord,

Would that code work in a template (.xlt) file as well? I've tried various
methods in the past and had all but given up.