Invoice Template and Database System

[ROSH22]

Hey,

I am looking to create a system where by.

The workbook will act as a template for invoice creation and also a database for all created invoices.

It will have a 'save' button. Upon pressing the button, the information filled out on the invoice form, such as Customer Name, Date, Order Value etc. are moved into a database in a separate sheet in the same workbook, and also a save prompt comes up, allowing the user to save the invoice they created. Once the invoice file is closed, the template clears itself for the next entry, but the information in the database remains.

I have uploaded the file here http://www.filedropper.com/invoicesystem, since I cannot upload to this forum.

When the button is pushed, I would like a 'save as' dialog to pop up, so that the person can save the invoice they created, but not the 'invoice database', also at the same time, the information in the cells marked red get pushed to the database in the second sheet. Then, the next time someone opens the file, the first sheet should be blank and ready to enter a new invoice.


Does anyone know how to go about this?

Thanks for your help folks at mrexcel

 

[bertie]

To test the code below make a copy of your workbook.
There are three proceedures in the code which are placed in a standard module.

Press Alt+F11 to open the VBA editor
Click Insert => Module
Copy and paste the code below.

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Module[/COLOR]
 
 [COLOR=darkblue]Sub[/COLOR] SaveInvoice()
   [COLOR=darkblue]Dim[/COLOR] sFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]  [COLOR=green]'based on Invoice id[/COLOR]
 
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] exitHere
   Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
 
   sFile = Sheets("Invoice").Range("B4").Value
 
   [COLOR=green]'copy and save the Invoice sheet[/COLOR]
   Sheets("Invoice").Copy
   Application.Dialogs(xlDialogSaveAs).Show
   ActiveWorkbook.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
 
exitHere:
   Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
 [COLOR=darkblue]Sub[/COLOR] SaveToDatabase()
   [COLOR=darkblue]Dim[/COLOR] wsData [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsInvoice [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]       [COLOR=green]'output row[/COLOR]
 
   [COLOR=green]'save to database[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsData = Sheets("Invoice Database")
   [COLOR=darkblue]Set[/COLOR] wsInvoice = Sheets("Invoice")
 
   [COLOR=darkblue]With[/COLOR] wsData
      rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
      .Range("A" & rw).Value = wsInvoice.Range("B3").Value
      .Range("B" & rw).Value = wsInvoice.Range("B4").Value
      .Range("C" & rw).Value = wsInvoice.Range("B5").Value
      .Range("D" & rw).Value = wsInvoice.Range("B6").Value
      .Range("E" & rw).Value = wsInvoice.Range("B7").Value
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsData = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsInvoice = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
Sub ClearInvoice()
   Sheets("Invoice").Range("B3:B7").ClearContents
[COLOR=darkblue]End[/COLOR] Sub

The code to process each of these procedures goes into the "Invoice" Sheet module.
Double click the command button on the spreadsheet to open up the VBA sheet module and insert this code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=red]CommandButton1[/COLOR]_Click()
   SaveInvoice
   SaveToDatabase
   ClearInvoice
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

NB Edit the name of the command button if necessary.

Click somewhere inside the code for the command button and press F8 to step through the code.

Hope this helps,
Bertie