VBA letter writing project

[wayneooo]

Hello,

I'm working on a project to help things run more smoothly for my business. We receive a large volume of letters from clients so i really need to find a way to make it more efficient. I'm not very experienced in VBA so looking for some help. The concept is quite simple though getting it to work correctly is another matter entirely!

Thankyou in advance to anybody who can assist.

Ok, i have an excel 2007 workbook. On the first sheet, i have added the following headings or sections:

DATE (single field - determines "documents required")

DOCUMENTS REQUIRED (IF DATE RANGE formula, displays the "documents required"

DOCUMENTS CHECKER (3 x checkboxes. These may alter 'responses', which i'll come to in a moment)

COMPLAINTS (several stacked comboboxes, input range linked to static lists on another worksheet. Each combobox links to the same list, user can select data in some or all of the comboboxes. However, *no two comboboxes can have the same selection*)

ADDITIONAL EVIDENCE (several checkboxes, which should only appear when certain conditions are met i.e. "complaints" and "date". For example, if a complaint 1 - "customer did not like the quality of paint" was selected and the date entered was between 1st June 2004 and 18th November 2006, then certain additional evidence is required such as for example, "Was the customer given an after sales questionnaire?". This evidence can be a simple yes or no, thus a single checkbox is used. Tick being YES, unticked being NO)

Finally there is a button that runs the code and generates the output.

RESPONSES

This project aside - currently, to determine the contents of a letter to my customers, i use a decision making flowchart. This asks the dates in question, what documents are present and sometimes additional evidence. At the end of the flowchart we come to a response which is then copy and pasted into Microsoft Word. Each complaint is addressed on the decision making flow and again each response is individually copy and pasted into a preformatted letter.

THE CURRENT PROBLEM

This process takes a significant amount of time and is open to many errors due to the constant switching back and forth, following the decision making flowchart, answering the additional evidence questions by checking paperwork, copying and pasting responses individually, and so on.

MY SOLUTION

Create a single excel sheet where user enters date, complaint points and answers any additional evidence questions immediately. The code then runs, follows the determined decision flow and appends each complaint point response into a formatted word document.

The process flow followed on the decision making flowchart for each complaint point is needed for auditing purposes, so a final function of the code would be to list in a new worksheet the decision flow followed for each complaint point.

I NEED TO KNOW:

- How to show checkboxes depending on the contents of a cell, and hide the checkboxes otherwise. i.e. IF C3="YES" THEN SHOW CHECKBOX1, CHECKBOX2, ELSE HIDE.

- How to prevent user from selecting the same complaint point more than once in the multiple, single comboboxes. If a complaint point is selected that's already selected in another combobox, a msgbox appears warning about it.

- How to show checkboxes for "additional evidence" depending on the selected contents of a combobox and also the contents of a cell.

i.e. Entered date is between 1st Jan 2000 and 1st March 2001 which i can call Phase 1 by using IF(AND(A4>=DATE(2000,1,1),A4<=DATE(2001,3,1)),"Phase 1",IF(AND(A4>=DATE..etc.

I then want to say something like IF COMPLAINT POINT (in combobox) = 2 and DATE = Phase 1 THEN SHOW CHECKBOX3, ELSE HIDE. And IF COMPLAINT POINT = 3 and DATE = Phase 1 THEN SHOW CHECKBOX4, ELSE HIDE, etc.

- How to create a macro for resetting the sheet; setting blanks for all complaint points (blank is top of the list) and hiding all checkboxes.

- How to make the decision flow followed be displayed on a new worksheet.

i.e.

Complaint Point 2 - Seller did not advise the cost

Flow:

Cost ZC: ZC(1) > ZC(2) > ZC(3) > checkbox3 ticked? Yes = ZC(4) ELSE ZC(5) > ZC(7)

Each code i.e. ZC(1) corresponds to a response:

ZC(1)

"Your concern:

You addressed us with your concern regarding the costs.

Our response:

I am very sorry you have been disappointed by our service. We aim to provide full cost details upon receipt of initital job quotation"

or

ZC(4)

Your concern:

"You addressed us with your concern regarding the costs

Our response:

I am very sorry you have been disappointed by our service. We aim to provide full cost details upon receipt of initial job quotation. However it seems you have not provided your quotation reference. I look forward to hearing from you"


--

Thanks to all for any assistance with this!