Excel Quotation Template - Adding some VBA functions

Good Afternoon Experts,

I have a basic quotation excel template that I have created and I have attached for your attention.

In principal I am trying to add some functionality to the spreadsheet to make it interactive and strong proof.

On the 1st Worksheet it shows you basic the layout of the quotation...

I would like to be able to create form so that the user can enter the data in the form and it gets entered directly in the template e.g. Company Name, Address, Post Code, Reference,

The item section is next, the user can enter an description about the item but I want to able to have a button so that you add another item or items that follows each other

Item 1
Item 2
Item 3

The other two sections are called 'Special Conditions' & 'Terms & Conditions' - this block of text is standard and it needs to follow on after the items section

Therefore, these two sections are always at the end of the document.

So when the quotation has been completed - I would like a button to validate the quote...

When the button is pressed - creates a record on the second worksheet with the quotation number and other details from the quotation.

When another quotation is started it should automatically increment the quotation number by + 1

I don't know if any of this is possible but I am looking for some help

Template attached

Nick CollinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
There's a missing macro, so it debugs.

I haven't seen your previous questions but I'll make some suggestions how I have created similar workbooks

1. I would save all the relevant information from the quote sheet to log, i.e only use one workbook
2. Logged quotes could be recalled using this data, probably just by using formulas. Usually with a second daat sheet to record line details for each quote. Each quote would have a unique number generated
3. And/or save each quote as a pdf file so that you can quickly look up the completed quote

The attached example shows demonstartes how to save a copy as pdf and increment the number. It's set up for an invoice but wouldn't require much chaning to use as a Quote
Roy CoxGroup Finance ManagerCommented:
I've added some simple code to save to your log

Let me know what you want changing or if you need help combining the code.
Nick CollinsAuthor Commented:
Thanks for your help... I would like to some help to combine the code..

Does the code refer to ini file?

I would like to be able to set the path of the archived quotations are stored and being able to set the quotation number sequence
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roy CoxGroup Finance ManagerCommented:
The code does not use an ini file for the number. As I said I would only store the quote data in one workbook, not multiple quote workbooks.

I'm not sure why you would want to set the path each time, it makes more sense to keep any generated files in one place.

How do you want to set the number sequence?
Nick CollinsAuthor Commented:
Yes I understand what you are trying to say...

I want to be able to add a file path as a parameter in the code. So that I can set the file path as a one off..

I am not changing it every time a quote is created.

Quotation sequence starts from 100000
Roy CoxGroup Finance ManagerCommented:
The filepath is simple, just provide the path and I'll add it or indicate where and how you change it.

THe file number simply needs starting from 100000.

I'll add the code to your example
Nick CollinsAuthor Commented:
If you just indicate on where it has to go that would be fine thank you
Roy CoxGroup Finance ManagerCommented:
I'll post an example for you later
Roy CoxGroup Finance ManagerCommented:

Just checking my example and I actually posted one that does save the number to an ini file. Do you want to stick with this or shall I use code that gets the number from the Log?
Nick CollinsAuthor Commented:
Get the number from the log would be better
Roy CoxGroup Finance ManagerCommented:
Hi Nick

Take a look at this example. I think it will be better for what you need.

The Excel Invoice Workbook consist of a template worksheet which you can change as much as you want, add your business details, change the colours, etc. However, the cell I7 must contain the invoice number or else the code will not work properly. You can use a different cell but you must remember to change the code accordingly. The template sheet has a shape with the first macro attached to it which acts as a button to run the code.

Macro #1: Creates the a new sheet and generate the next invoice number. When the new sheet is created the code also deletes the add new sheet button as it will not be needed on this sheet. At the same time it makes a different button visible. This button runs a macro to save the invoice to a new workbook.

Macro #2 provides an option to save the new sheet as a separate workbook. The code requires a separate folder named “Invoice Archive” to be held within the folder that contains the Invoice workbook. When used the macro saves and renames the new invoice to a new workbook in this folder. The button is removed as it will no longer be needed and the code will not work in the new workbook.

As the name implies this example although usable is extremely basic. A true invoicing program should have databases  for customers, invoice records, sales data. In addition it should be possible to email the invoice to your customers as a pdf file. It is also desirable to be able to produce quotes and credit notes.
Nick CollinsAuthor Commented:
I think I prefer your earlier example...
Roy CoxGroup Finance ManagerCommented:
Do you mean the code or the layout?
Nick CollinsAuthor Commented:
Saving a local copy of the invoice, adding a log of the record and providing a new number
Roy CoxGroup Finance ManagerCommented:
That's what the code does in the last example.

It uses the empty form as a Template from which it creates a copy with an incremented number, then saves a copy of that form as a one sheet workbook to a folder of your choice
Nick CollinsAuthor Commented:
I understand on how the process works...

On the template worksheet - you press the button to create a copy of the worksheet and it allocates a new number

Then you click to save the active workbook I get an error message and it points to the following code

 ActiveWorkbook.SaveAs InvNm, FileFormat:=xlOpenXMLWorkbook

-  In your example how can I specify the location of where I want the worksheet to be saved
-  Where in the code can I specify the start of the number
Roy CoxGroup Finance ManagerCommented:
Hi Nick

The code currently uses a pre-set filepath, have a look at this amended code

Sub ARchive()

    Const FilePath As String = "put your file path in here" ''/// remember closing \
    Dim InvNm As String ''/// name to save copy as, uses Invoice Number
    ' Copy Invoice to a new workbook
    With ActiveSheet
        'get name and path for new workbook
        'use .xls for earlier versions of Excel
        InvNm = FilePath & .Range("I7").Value & ".xlsx"
        .Shapes("btnSave").Delete    '<- remove the save button
        'ActiveWorkbook.SaveAs InvNm '- use for earlier versions of Excel
        'use following line for versions of Excel since Excel 2007
        ActiveWorkbook.SaveAs InvNm, FileFormat:=xlOpenXMLWorkbook
        ''/// optional to delete the worksheet that has been saved
'        Application.DisplayAlerts = False
'        ActiveSheet.Delete
'        Application.DisplayAlerts = True
    End With
End Sub

Open in new window

The invoice number is generated by using the current invoice number and adding 1. I've amended the code to ensure it runs from 100000

Sub Add_New_Sheet()
'hide operation from user
    Application.ScreenUpdating = False
    On Error GoTo exit_proc  '<- ensure screen updating is restored if an error occurs
    With ThisWorkbook.Worksheets("Template")
        If .Range("I7").Value < 100000 Then
            .Range("I7").Value = 100000
            .Range("I7").Value = .Range("I7").Value + 1    '<- increment the Invoice number
        End If
        .Copy after:=Worksheets(1)
    End With
    'name the new sheet
    With ActiveSheet
        .Name = "Invoice" & .Range("I7").Value
        .Shapes("btnNew").Delete    '<- remove the add new sheet button
        .Shapes("btnSave").Visible = True
    End With
    Application.ScreenUpdating = True
End Sub

Open in new window

Nick CollinsAuthor Commented:
Thank you...

I am applying the code to my new template..

Regarding the buttons I have the applied the new invoice button the worksheet template and where do I save the button save workbook.
Roy CoxGroup Finance ManagerCommented:
I would put it on the log sheet.

Get it finished and I'll check everything for you. I'm out tonight so I might not respond immediately.
Nick CollinsAuthor Commented:

I have attached my template - all the code is there but I need some help applying the buttons..

Roy CoxGroup Finance ManagerCommented:
Check this. I've added buttons and assigned the macros
Nick CollinsAuthor Commented:
Thanks I've got it working perfectly...

When it saves an copy of the worksheet... can I add a procedure so it copies a PDF version too
Roy CoxGroup Finance ManagerCommented:
Yes. I would suggest that you only save a pdf copy, unless you want to open a saved workbook for editing.
Nick CollinsAuthor Commented:
I want to have two versions PDF & Worksheet copies - as I'll estimators always wants to edit the worksheet at a later on some quotations
Roy CoxGroup Finance ManagerCommented:
I'll write you some code to do this, I would keep separate folders for pdf and excel.
Roy CoxGroup Finance ManagerCommented:
Nick check this.

I don't know whether I uploaded the wrong workbook last night so you might want to attach your lates file

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
No further response so presumably completed
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.