First, the depth of resources on this site has been a true God-Send. You guys are awesome! I couldn't begin to tell you how many times I've found just the answer I needed quickly here! THANK YOU!
I’ve inherited a spreadsheet that is used as a quoting tool for salespeople. They make 8-10 selections from drop down lists and enter a few quantities; a pricing algorithm cranks out and displays the price next to the combo boxes; and then they click a “Export to PDF” button on the form to magically print an area in the current worksheet. The output is accurate and contains correct data, but is not easily readable nor particularly user friendly.
My objectives are 3-fold and are listed in order of importance to the operation. I started to make this 3 separate posts, but wanted to keep things in context. I can certainly split it up as needed if that is a better approach.
1. (Absolute Must) Have the “Export to PDF” command button print a much better looking document – perhaps a different worksheet that has been formatted better or create a better looking PDF in the code if that is easier or something completely different. I (sort of) don't care what the approach is)
a. In an ideal world, the output report will display from 1 to 10 line items but the spacing above and below won’t have blank lines for items not on the quote. This is not a requirement, but a nice-to-have.
b. Quote Example:
i. Header: LOGO.JPG “Services Quote”
ii. Section 1: 3-4 lines of marketing propaganda
iii. Section 2: “Section Header1” “Quantity” “Price 1” “Price 2”
iv. Section 3: 1-3 lines of data including “LineItemDescription” Qty Price1 Price2
v. Section 4: “Section Header2”
vi. Section 5: 1-3 lines of data including “LineItemDescription” Qty Price1 Price2
vii. Section 6: “Section Header 3” “Data1Title” Data2Title” “Data3Title”
viii. Section 7: 1-3 lines of data including “LineItemDescription” Qty Price1 Price2
ix. Section 8: Total Line “text” Qty TotalPrice1 TotalPrice2 ßThese are simply data fields, no calc required here
x. Section 9: 5 lines of marketing rubbish
xi. Section 10: 2 “columns” Left side of page more marketing text, Right side: Multi-line text box
xii. Section 11: 1 big line of marketing stuff
xiii. Section 12: The fine print
xiv. Section 13: Footer text (static)
2. (Really Nice to Have) Add an “Email” command button that pulls the email addresses from 2 fields; creates the same PDF as above; and sends the PDF in an email with the To: field being one of the email addresses (customer’s) and the BCC: field containing the other (salesperson’s email). We use Outlook 2007
a. In an ideal world, the email would already be written in the code with an option to add an introductory paragraph if desired.
3. (Optional, but very handy) Save the information that was input (Customer info, items quoted, etc.) to a table on another worksheet yet to be created so the salesman can have easy access to historical quotes.
The 4 worksheets that are important in the spreadsheet are
1. Pricing Guide: This is the one the salespeople live in.
2. Data: This is a locked and hidden worksheet that is used to calculate the pricing.
3. Quote: This is the new worksheet that pulls the data from Pricing Guide, adds labels, disclaimers, promotions, etc but is formatted for customer viewing.
4. Quote History: One record for each quote created above.
I’m great with many things Excel, but am very weak with VBA.
Excel 2007. Windows 7
Thanks again,
Cory
I’ve inherited a spreadsheet that is used as a quoting tool for salespeople. They make 8-10 selections from drop down lists and enter a few quantities; a pricing algorithm cranks out and displays the price next to the combo boxes; and then they click a “Export to PDF” button on the form to magically print an area in the current worksheet. The output is accurate and contains correct data, but is not easily readable nor particularly user friendly.
My objectives are 3-fold and are listed in order of importance to the operation. I started to make this 3 separate posts, but wanted to keep things in context. I can certainly split it up as needed if that is a better approach.
1. (Absolute Must) Have the “Export to PDF” command button print a much better looking document – perhaps a different worksheet that has been formatted better or create a better looking PDF in the code if that is easier or something completely different. I (sort of) don't care what the approach is)
a. In an ideal world, the output report will display from 1 to 10 line items but the spacing above and below won’t have blank lines for items not on the quote. This is not a requirement, but a nice-to-have.
b. Quote Example:
i. Header: LOGO.JPG “Services Quote”
ii. Section 1: 3-4 lines of marketing propaganda
iii. Section 2: “Section Header1” “Quantity” “Price 1” “Price 2”
iv. Section 3: 1-3 lines of data including “LineItemDescription” Qty Price1 Price2
v. Section 4: “Section Header2”
vi. Section 5: 1-3 lines of data including “LineItemDescription” Qty Price1 Price2
vii. Section 6: “Section Header 3” “Data1Title” Data2Title” “Data3Title”
viii. Section 7: 1-3 lines of data including “LineItemDescription” Qty Price1 Price2
ix. Section 8: Total Line “text” Qty TotalPrice1 TotalPrice2 ßThese are simply data fields, no calc required here
x. Section 9: 5 lines of marketing rubbish
xi. Section 10: 2 “columns” Left side of page more marketing text, Right side: Multi-line text box
xii. Section 11: 1 big line of marketing stuff
xiii. Section 12: The fine print
xiv. Section 13: Footer text (static)
2. (Really Nice to Have) Add an “Email” command button that pulls the email addresses from 2 fields; creates the same PDF as above; and sends the PDF in an email with the To: field being one of the email addresses (customer’s) and the BCC: field containing the other (salesperson’s email). We use Outlook 2007
a. In an ideal world, the email would already be written in the code with an option to add an introductory paragraph if desired.
3. (Optional, but very handy) Save the information that was input (Customer info, items quoted, etc.) to a table on another worksheet yet to be created so the salesman can have easy access to historical quotes.
The 4 worksheets that are important in the spreadsheet are
1. Pricing Guide: This is the one the salespeople live in.
2. Data: This is a locked and hidden worksheet that is used to calculate the pricing.
3. Quote: This is the new worksheet that pulls the data from Pricing Guide, adds labels, disclaimers, promotions, etc but is formatted for customer viewing.
4. Quote History: One record for each quote created above.
I’m great with many things Excel, but am very weak with VBA.
Excel 2007. Windows 7
Thanks again,
Cory
