I need a cell to change numbers each time it is opened up.

[Bcoleman75]

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]

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]

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?

 

[Gord Dibben]

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.

File>Save As>File Type>Template(*.xlt)


Gord Dibben MS Excel MVP

 

[Mike]

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

 

[Bcoleman75]

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]

Where do I send it to

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!!!!

 

[Bcoleman75]

sorry i should have sent email address

(e-mail address removed)

thks,

bryan

 

[Mike]

Or you could send you Sheet to (e-mail address removed)

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!!!!

 

[Bcoleman75]

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.

(e-mail address removed)

 

[Riker1074]

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.

 

[Gord Dibben]

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