I have an invoice in excel 2007 template in which I want to increment the
invoice number everytime I save it. Anybody who know how to do this, is
greatly appreciated. I'm not familiar with Microsoft Visual Basic Editor,
but if I can get instructions on how to do this I can manage to do it. thanks

I have never tried this myself, but I've read a few posts (here) that refer
other people to this:
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

Regards,
Ryan---

--
RyGuy


"Arod" wrote:

> I have an invoice in excel 2007 template in which I want to increment the
> invoice number everytime I save it. Anybody who know how to do this, is
> greatly appreciated. I'm not familiar with Microsoft Visual Basic Editor,
> but if I can get instructions on how to do this I can manage to do it. thanks

Thanks ryguy7272,

The things is that I'm not familiar with the codes on VBE so i don't know
what to put. Also, I want the invoice number to increment automatically when
I save the file.

"ryguy7272" wrote:

> I have never tried this myself, but I've read a few posts (here) that refer
> other people to this:
> http://www.mcgimpsey.com/excel/udfs/sequentialnums.html
>
> Regards,
> Ryan---
>
> --
> RyGuy
>
>
> "Arod" wrote:
>
> > I have an invoice in excel 2007 template in which I want to increment the
> > invoice number everytime I save it. Anybody who know how to do this, is
> > greatly appreciated. I'm not familiar with Microsoft Visual Basic Editor,
> > but if I can get instructions on how to do this I can manage to do it. thanks

Take a look at this:
http://www.anthony-vba.kefra.com/vba...ur_First_Macro


Regards,
Ryan---

--
RyGuy


"Jeff Johnson" wrote:

> "Arod" <(E-Mail Removed)> wrote in message
> newsFE77103-28D0-4A34-88FD-(E-Mail Removed)...
>
> > The things is that I'm not familiar with the codes on VBE so i don't know
> > what to put. Also, I want the invoice number to increment automatically
> > when
> > I save the file.
>
> With the workbook active, open the VB Editor (Alt+F11). You should see a
> tree view with a node that says something like "VBAProject (<workbook
> name>)". Find the ThisWorkbook node below it and double-click it. A code
> editor will appear.
>
> You should see two dropdowns above the code editor. Drop down the left one.
> Click the Workbook item. Now, in the right dropdown, select BeforeSave.
> Enter the following code between Private... and End Sub lines (on its own
> line):
>
> Worksheets("<name of sheet containing your number>").Range("<address of the
> cell you want to update>").Value = Worksheets("<same worksheet
> name>").Range("<same address>").Value + 1
>
> Save the workbook.
>
> Please note that if the workbook has not been saved before (or the user
> chooses Save As), this value will get incremented EVEN IF the user cancels
> the Save dialog.
>
>
>

I tried following your directions and typed in the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheet("Receivingtemplate2.xlt").Range("G2").Value=Worksheets("Receiving
template.xlt").Range("G2
").Value + 1


Private Sub Workbook_Open()

End Sub

And I am getting a syntax error. Can you tell from this what I am doing
wrong?
Thanks!



"Jeff Johnson" wrote:

> "Arod" <(E-Mail Removed)> wrote in message
> newsFE77103-28D0-4A34-88FD-(E-Mail Removed)...
>
> > The things is that I'm not familiar with the codes on VBE so i don't know
> > what to put. Also, I want the invoice number to increment automatically
> > when
> > I save the file.
>
> With the workbook active, open the VB Editor (Alt+F11). You should see a
> tree view with a node that says something like "VBAProject (<workbook
> name>)". Find the ThisWorkbook node below it and double-click it. A code
> editor will appear.
>
> You should see two dropdowns above the code editor. Drop down the left one.
> Click the Workbook item. Now, in the right dropdown, select BeforeSave.
> Enter the following code between Private... and End Sub lines (on its own
> line):
>
> Worksheets("<name of sheet containing your number>").Range("<address of the
> cell you want to update>").Value = Worksheets("<same worksheet
> name>").Range("<same address>").Value + 1
>
> Save the workbook.
>
> Please note that if the workbook has not been saved before (or the user
> chooses Save As), this value will get incremented EVEN IF the user cancels
> the Save dialog.
>
>
>

No, probably not---1st time user ;-)

"Jeff Johnson" wrote:

> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:878B32EE-9573-451C-9C84-(E-Mail Removed)...
>
> > Take a look at this:
> > http://www.anthony-vba.kefra.com/vba...ur_First_Macro
>
> Did you intend to reply to ME?
>
>
>

"Jeff Johnson" <(E-Mail Removed)> wrote in message
news:5Z6dnal8Z4UdiIbVnZ2dnUVZ_oKhnZ2d@datapex...
> "phale" <(E-Mail Removed)> wrote in message
> news:EBA92834-343E-4AC8-A092-(E-Mail Removed)...
> >I tried following your directions and typed in the following code:
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> >
Worksheet("Receivingtemplate2.xlt").Range("G2").Value=Worksheets("Receiving
> > template.xlt").Range("G2
> > ").Value + 1
> >
> >
> > Private Sub Workbook_Open()
> >
> > End Sub
> >
> > And I am getting a syntax error. Can you tell from this what I am doing
> > wrong?
>
> Is that your EXACT code (i.e., did you copy and paste)? Because if so, the
> first "Worksheets" is missing the "s" at the end.
>
> Also, you appear to be trying to use the name of the WORKBOOK (i.e., the
> file name) as an argument to the Worksheets() function instead of the name
> of the SHEET as I told you. The workbook itself will already be open; you
> don't need to reference it.
>

Just to add -
In addition to the missing "s" after Worksheet* you (OP) have another typo
for the sheet name (strange to name a sheet like a workbook-template name)

"Receivingtemplate2.xlt" vs "Receivingtemplate.xlt"

Sort out you spellings, then try something like this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

With Worksheets("Sheet1").Range("A1")
.Value = .Value + 1
End With

End Sub

Obviously change "Sheet1" and "A1" as required.

Regards,
Peter T

Yes I did copy and paste--and when I got the syntax error, I tried making
changes like taking the s off the worksheets, etc.
I know so little about this I just assumed the file name should be
referenced. Am in the process of reading the VBE help file and taking the
tutorial you recommended.

So. . .would this code be correct?

Thanks for your help!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> > Worksheets("Sheet1").Range("G2").Value=Worksheets("Sheet1").Range("G2
> > ").Value + 1
> >

"Jeff Johnson" wrote:

> "phale" <(E-Mail Removed)> wrote in message
> news:EBA92834-343E-4AC8-A092-(E-Mail Removed)...
> >I tried following your directions and typed in the following code:
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> > Worksheet("Receivingtemplate2.xlt").Range("G2").Value=Worksheets("Receiving
> > template.xlt").Range("G2
> > ").Value + 1
> >
> >
> > Private Sub Workbook_Open()
> >
> > End Sub
> >
> > And I am getting a syntax error. Can you tell from this what I am doing
> > wrong?
>
> Is that your EXACT code (i.e., did you copy and paste)? Because if so, the
> first "Worksheets" is missing the "s" at the end.
>
> Also, you appear to be trying to use the name of the WORKBOOK (i.e., the
> file name) as an argument to the Worksheets() function instead of the name
> of the SHEET as I told you. The workbook itself will already be open; you
> don't need to reference it.
>
>
>

Jeff, I tried what you told me to do and it worked perfect. Thank you so
much for your help.

"Jeff Johnson" wrote:

> "Arod" <(E-Mail Removed)> wrote in message
> newsFE77103-28D0-4A34-88FD-(E-Mail Removed)...
>
> > The things is that I'm not familiar with the codes on VBE so i don't know
> > what to put. Also, I want the invoice number to increment automatically
> > when
> > I save the file.
>
> With the workbook active, open the VB Editor (Alt+F11). You should see a
> tree view with a node that says something like "VBAProject (<workbook
> name>)". Find the ThisWorkbook node below it and double-click it. A code
> editor will appear.
>
> You should see two dropdowns above the code editor. Drop down the left one.
> Click the Workbook item. Now, in the right dropdown, select BeforeSave.
> Enter the following code between Private... and End Sub lines (on its own
> line):
>
> Worksheets("<name of sheet containing your number>").Range("<address of the
> cell you want to update>").Value = Worksheets("<same worksheet
> name>").Range("<same address>").Value + 1
>
> Save the workbook.
>
> Please note that if the workbook has not been saved before (or the user
> chooses Save As), this value will get incremented EVEN IF the user cancels
> the Save dialog.
>
>
>

Okay this is what I have based on the earlier advice:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("Sheet1").Range("G2").Value =
Worksheets("Sheet1").Range("G2").Value + 1

End Sub

Which gives me a runtime error 9 that says subscript out of range

The advice in the last post:
..Value=.Value+1

"Peter T" wrote:

> "Jeff Johnson" <(E-Mail Removed)> wrote in message
> news:5Z6dnal8Z4UdiIbVnZ2dnUVZ_oKhnZ2d@datapex...
> > "phale" <(E-Mail Removed)> wrote in message
> > news:EBA92834-343E-4AC8-A092-(E-Mail Removed)...
> > >I tried following your directions and typed in the following code:
> > >
> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > Boolean)
> > >
> Worksheet("Receivingtemplate2.xlt").Range("G2").Value=Worksheets("Receiving
> > > template.xlt").Range("G2
> > > ").Value + 1
> > >
> > >
> > > Private Sub Workbook_Open()
> > >
> > > End Sub
> > >
> > > And I am getting a syntax error. Can you tell from this what I am doing
> > > wrong?
> >
> > Is that your EXACT code (i.e., did you copy and paste)? Because if so, the
> > first "Worksheets" is missing the "s" at the end.
> >
> > Also, you appear to be trying to use the name of the WORKBOOK (i.e., the
> > file name) as an argument to the Worksheets() function instead of the name
> > of the SHEET as I told you. The workbook itself will already be open; you
> > don't need to reference it.
> >
>
> Just to add -
> In addition to the missing "s" after Worksheet* you (OP) have another typo
> for the sheet name (strange to name a sheet like a workbook-template name)
>
> "Receivingtemplate2.xlt" vs "Receivingtemplate.xlt"
>
> Sort out you spellings, then try something like this
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> Cancel As Boolean)
>
> With Worksheets("Sheet1").Range("A1")
> .Value = .Value + 1
> End With
>
> End Sub
>
> Obviously change "Sheet1" and "A1" as required.
>
> Regards,
> Peter T
>
>
>