Auto increment invoice number based on year and month

[Guest]

Hello,

I am working on an invoice worksheet in which I would like the invoice number to be incremented
automatically based on the date. For instance, if the month is March and year is 2004, then I would
like the invoice number to read: 0403001. This number should be incremented by 1 each time the
file is opened: 0403002, 0403003, etc... When a new month (or year) comes around, then I
would like for the last 3 digits to rollback to "001". So, for April, 2004, it should read: 0404001.
Anyone have any ideas about this? I have searched various websites, and newsgroups, but
have never found anything discussing something like this. Thanks very much!

 

[Frank Kabel]

Hi
try the following macro (put it in your workbook module):

Private Sub Workbook_Open()
Dim wks As Worksheet
Dim rng As Range
Dim old_value
Dim new_value
Dim counter
Dim m_value
Dim y_value

Set wks = ActiveWorkbook.Worksheets("Tabelle1")
Set rng = wks.Range("A1")
old_value = rng.Value

'parse old entry
If old_value = "" Then
new_value = Format(Now, "YYMM") & "001"
Else
counter = --Right(old_value, 3)
m_value = --Mid(old_value, 3, 2)
y_value = --Left(old_value, 2)
If CDbl("20" & Format(y_value, "00")) <> Year(Now) Or m_value <>
Month(Now) Then
new_value = Format(Now, "YYMM") & "001"
Else
new_value = Format(Now, "YYMM") & Format(counter + 1, "000")
End If
End If
rng.NumberFormat = "@"
rng.Value = Format(new_value, "0000000")
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

Hello,

I am working on an invoice worksheet in which I would like the

invoice number to be incremented

automatically based on the date. For instance, if the month is March and year is 2004, then I would
like the invoice number to read: 0403001. This number should be incremented by 1 each time the
file is opened: 0403002, 0403003, etc... When a new month (or year) comes around, then I
would like for the last 3 digits to rollback to "001". So, for

April, 2004, it should read: 0404001.

Anyone have any ideas about this? I have searched various websites, and newsgroups, but
have never found anything discussing something like this. Thanks very much!




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via

Encryption =---

 

[norika]

I have a similar problem of invoice number increment.

As I design a template for users, they are not too familiar with Marc
or they set the Macro security is high. I dont want to change thei
preference or it is not possible to help them to change as there ar
more than 200 users at all.

The question is how do I write a formula or other suggestions, not vba
to increase number when opening file. I tried to use now() function an
set it as text, but when the file opening again , the number will b
changed.

Thanks in advance.

norik

 

[Frank Kabel]

Hi norika
without VBA this is not possible. Sorry to say

 

[norika]

Frank,

Thank for your reply.

norik