Tags: auto, consequence, excel, greatly, invoice, microsoft, msdn, number, numbering, previous, software, worksheet

Auto invoice numbering

On Microsoft » Microsoft Excel

3,159 words with 3 Comments; publish: Fri, 23 May 2008 07:56:00 GMT; (30662.50, « »)

I need a auto invoice numbering on a new worksheet that can add a

consequence invoice number from the previous worksheet number.

Any help is greatly appreciated.

All Comments

Leave a comment...

  • 3 Comments
    • Hi

      do you really mean 'worksheet' and not 'workbook'. For the latter one

      you may have a look at the following repost. It assumes you have a

      invoice template (*.xlt file)

      --

      one way (using the Windows registry for storing the last number). Put

      the following code in the workbook module (not in a standard module) of

      your template:

      - It changes cell A1 of the first sheet

      - you may change the key identifiert according to your needs (e.g.

      DEFAULTSTART, MYLOCATION, etc.)

      Private Sub Workbook_Open()

      Const DEFAULTSTART As Integer = 1

      Const MYAPPLICATION As String = "Excel"

      Const MYSECTION As String = "myInvoice"

      Const MYKEY As String = "myInvoiceKey"

      Const MYLOCATION As String = "A1"

      Dim regValue As Long

      With ThisWorkbook.Sheets(1).Range(MYLOCATION)

      If .Text <> "" Then Exit Sub

      regValue = GetSetting(MYAPPLICATION, MYSECTION, _

      MYKEY, DEFAULTSTART)

      .Value = CStr(regValue)

      SaveSetting MYAPPLICATION, MYSECTION, MYKEY, regValue + 1

      End With

      End Sub

      --

      Regards

      Frank Kabel

      Frankfurt, Germany

      "unkown" <jshim168.excel.todaysummary.com.hotmail.com> schrieb im Newsbeitrag

      news:niscc.35943$Ig.27302.excel.todaysummary.com.pd7tw2no...

      > I need a auto invoice numbering on a new worksheet that can add a

      > consequence invoice number from the previous worksheet number.

      > Any help is greatly appreciated.

      >

      #1; Fri, 23 May 2008 07:57:00 GMT
    • Using the registry means that the workbook can be used only on the PC whose registry holds the invoice number counter.
      #2; Fri, 23 May 2008 07:58:00 GMT
    • Not very elegant, but easy to set up:

      * what you need!

      1 excel WB called "Invoice.xls" in which there are 2 sheets, "Invoice"

      and "Data"

      Concept:

      You store a number in the data page which is updated when somthing

      happens:

      code bit one:

      sheets("data").cells(1,1).value = sheets("data").cells(1,1).value +1

      code bit 2

      sheets("Invoice").Range("a2").value = sheets("data").cells(1,1).value

      so this puts the number in a2 on the invoice sheet, which you format

      as you like;

      now where do you put these code?

      well you can put code 2 in the open workbook event, and the code one

      bit in the

      before close, and you get a book that opens with a new invoice number

      and updates it on each open/close,

      Better, to put the 2 codes buttons on the Invocie sheet, and then you

      can make as many invoice as you like, with out opening closing

      so you get this

      Private Sub CommandButton1_Click()

      Sheets("data").Cells(1, 1).Value = Sheets("data").Cells(1, 1).Value +

      1

      Sheets("Invoice").Range("a2").Value = Sheets("data").Cells(1, 1).Value

      End Sub

      ross

      "unkown" <jshim168.excel.todaysummary.com.hotmail.com> wrote in message news:<niscc.35943$Ig.27302.excel.todaysummary.com.pd7tw2no>...

      > I need a auto invoice numbering on a new worksheet that can add a

      > consequence invoice number from the previous worksheet number.

      > Any help is greatly appreciated.

      #3; Fri, 23 May 2008 07:59:00 GMT