Need help, Seq numbers, Save filename & A button on sheet.

I use Excel for my Invoices and most all business info handling.

I use a unique type of Invoice / Job #. It is formatted like this: 06-001-01.
The "06" is the year. The "001" is the day of the year (001 - Jan 1st / 365 - Dec 31st). The final (2) digits "01" are the Job# for that day. (At this point I only need 2 digits, 01 - 99 jobs per day).

The number generated by the date 01/01/2006 is "06-001" (cell I17), then I have to change the "01" manually (to other numbers as needed) which is in cell (K17). BTW: (I17 & J17) are merged for a wider cell space.

When I select the cell that contains the date (B19), then press ( Ctrl & : ) it inserts todays date, then job number/date code are automatically generated.
However, the last 2 digits have to be manually changed.


What I want to do is:

1) Have last (2) digits "01", become sequential, automatically, each day. (No dup invoice numbers). Each "Save" will increase it by 1. (01, 02, 03, etc).

2) Use invoice number as worksheet filename, when saving.

3) Place a "Save" button on the worksheet. (when clicked, it saves using the invoice number.

If anyone can help with any part of this, I would greatly appreciate it.

I had another issue, but couldn't get an answer for that one. I will try that one again, later.

If you like challenges, I have a few more, after these.

Thanks,

Harv

see example: http://cjoint.com/?foxZ2HVIXz

1) Done in module1!NextInvoiceNo
Sub NextInvoiceNo()
With Range("A2")
If Date = Range("A1").Value Then
.Value = .Value + 1
Else
Range("A1").Value = Date
.Value = 1
End If
Range("C1").Value = _
Format(Date, "yy") & "-" & _
Format(Date - DateSerial(Year(Date), 1, 0), "000") & "-" & _
Format(.Value, "00")
End With
End Sub

NB:
A1 contains current date
A2 contains current invoice seq no.
Column A is hidden

NextInvoiceNo is called by Workbook_Open and Workbook_Before_Save

2) Done in ThisWorkbook.Before_Save:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.EnableEvents = False
Me.Save
Me.SaveCopyAs Range("c1").Value & ".xls"
Application.EnableEvents = True
NextInvoiceNo
Cancel = True
End Sub

3) No need for a button: just save Invoice.xls, and it will be saved under
the proper name.

HTH
--
AP

"Harv" <Harv.27tc5a_1147636201.2302@excelforum-nospam.com> a écrit dans le
message de news: Harv.27tc5a_1147636201.2302@excelforum-nospam.com...
>
> I use Excel for my Invoices and most all business info handling.
>
> I use a unique type of Invoice / Job #. It is formatted like this:
> 06-001-01.
> The "06" is the year. The "001" is the day of the year (001 - Jan 1st /
> 365 - Dec 31st). The final (2) digits "01" are the Job# for that day.
> (At this point I only need 2 digits, 01 - 99 jobs per day).
>
> The number generated by the date 01/01/2006 is "06-001" (cell I17),
> then I have to change the "01" manually (to other numbers as needed)
> which is in cell (K17). BTW: (I17 & J17) are merged for a wider cell
> space.
>
> When I select the cell that contains the date (B19), then press ( Ctrl
> & : ) it inserts todays date, then job number/date code are
> automatically generated.
> However, the last 2 digits have to be manually changed.
>
>
> What I want to do is:
>
> 1) Have last (2) digits "01", become sequential, automatically, each
> day. (No dup invoice numbers). Each "Save" will increase it by 1. (01,
> 02, 03, etc).
>
> 2) Use invoice number as worksheet filename, when saving.
>
> 3) Place a "Save" button on the worksheet. (when clicked, it saves
> using the invoice number.
>
> If anyone can help with any part of this, I would greatly appreciate
> it.
>
> I had another issue, but couldn't get an answer for that one. I will
> try that one again, later.
>
> If you like challenges, I have a few more, after these.
>
> Thanks,
>
> Harv
>
>
> --
> Harv
> ------------------------------------------------------------------------
> Harv's Profile:
> http://www.excelforum.com/member.php...o&userid=34344
> View this thread: http://www.excelforum.com/showthread...hreadid=541962
>