Word form and tracking spreadsheet

A word form and spreadsheet tracker macro required - word vba and excel vba expert required. For a detailed list of requirements - please see the below requirements in the deliverables section? plus my notes for important extra infomation - easy mistakes, importance of speed, need for full after support which is essential. For macro 1, I have attatched a PDF file so you can see what needs to be updated in the word doument. The transmital no is related to the spreadsheet tracker - column A. For macro'S 2-5 it should be obvious from viewing the word form and my description in delievrables? what extra fields in the word document need to be updated. For macro 6? - this is spreadshet only In total 6 very similar macros with minor ammendments in each one (so basically once you have cracked one - the others will follow with minor adjustments). Other attached files include a word doument of the form to test from and an example of a spreadsheet tracker. The bidder must be flexible to adjust to any minor adjustments and add on's not neccesarily written down for customisation. Its difficult to write all the requirements and details down but I have tried to write a comprehensive list of requirements. Please provide a demo video - showing this in action that you can do. If macros 1 and 5 could be added as a priority then that would be appreciated. This needs to work word 2003- word 2007. I will be testing on both areas. ## Deliverables Following my high level summary - please see the detailed requirements below: **User Interface form:** Supplier drop down combobox and a execute and cancel button. 4 check boxes/option buttons dictating the macro type - **Macro 1**- DTL - NLR, **Macro 2**: DTL NLR and company classification, **Macro 3** DTL - export Licence/company class untouched, **Macro4** - Extra dates - requestor section,? **Macro5** - Extra dates? requester - export untouched, **Macro 6** - Spreadsheet only. Also there is a need for a number/title/description text box - to populate spreadsheet column B There will be a need for an originator of request- option buttons times 6 - to populate spreadsheet column D - need to add 6 and potentially more so a combo box may be more suited? Ref spreadsheet - there could other action trackers on other different sheets with all with very similar information - so it advised there are checked boxes to select the sheet. For ease of use, can sheet index 1 always be checked upon form initialization as this will be the most popular choice. **Word and spreadsheet - Macro 1to5** Spreadsheet only - Macro 6 - this could be separated by a frame from macro' 1 to 5 and as I suggest below it could have its own combo supplier box. **The coder should know the best layout and judgment - this is only guideline only.** **Macro details** **Macro 1** On the already open word document - form Check if password protect exist If it does type 'Stacey' as password (With a capital S, the rest lower case) If not password protected just proceed as normal Error handle just in case the password [login to view URL] box return. Populate Export licence with "NLR". Important easier error 'NLR' doesnt exist on drop down from the base form. Populate request completed by with "Bob Jones" (Note this will be my name not real - so i need to be edit the code in the module and simply change this with my name). Populate DTL Received - Today date format "dd-mmm-yy". There are three dates in vertical order that need to be populated. Populate Transmittal No: Open the excel file in a set path C://. Find the first blank cell in column B - against the first blank cell and return the tracking number in column A. Populate the Transmittal No with the value from column A. The spreadsheet path will be fixed - it will be in whatever sub folder/folder. After this populate the spreadsheet at the first blank cell in column B against the tracking number we previously captured above. Note this is the first blank cell after row 4 as there could be some blank spaces at the top. Error handle to check if the spreadsheet is open by someone else, if not return an error message - very important - otherwise we cannot populate the spreadsheet. Populate the Number/Title/Issue - column B Populate Security Classification = Protect - column C Populate Originator of request - there will be three options button choices - in the form with names of people - column D Populate Company Classification - Commercial In confidence - column E Populate Recipient - Andy Lacey (This is different per supplier) - its basically the name of the supplier recipient - column F Populate Company - this is based on what the user selects as the Supplier from the combo box this actually the supplier - column G Populate Country of Origin - this is different per supplier - i.e UK ,Italy etc (again base on the supplier) - column H Populate Commercial Approver - this is 'Ben Jones' - not real will need to change this once base capability is achieved, this does need to form part of the code - Column I Populate Date of delivery - dynamic - today's Date - dd/mm/yyyy =- column J Populate Export Licence - 'NLR' - notice this is what was entered in the word earlier. - column k Populate Delivery Method - 'Email' - column L Populate Actual DTL Raised - 'Yes - column M Population complete Save excel file and close. **Macro 2** Same as macro 1 but populate an extra field in the form in the word doument. Populate Company classification with: 'Commercial in confidence' Note on the form this is a drop down box. **Macro 3** Same as macro 1 but on the word form leave export licence and company classification untouched and only populate the other word fields under the heading 'to be completed by DM section' i.e request completed by, transmital no and dates. **Macro 4** Same as macro 1 but populate 3 dates (today's date - format dd/mmm/yy) under the 'to be completed by requester' section in the word form. Dates vertically - shouild be obvious from the form. **Macro 5** Same as macro 4(i.e with extra dates) but leave the export compliance field untouched. **Macro 6:** Spreadsheet only Suggest for this one a separate supplier combo box as it will be less drop drown suppliers to choose from making it easier perhaps. Not sure if its worth adding a separate text boxes for the subject. I simply just want to populate the spreadsheet in the same manor description. Note each field could be customised per supplier as per macro 1. **Notes: ** Once I press the execute button: this needs to be completed in a few seconds to make it worth it - very sharp. Looping through the excel rows may not be quick enough because there could be 10000'S of excel rows to find the first blank cell in column B. Also considering the time it takes to complete the form i.e. enter number/title description in the form to begin with. I can't afford to be waiting minutes for this to be completed once I press the execute button as its only 8min job max if i was to do this manually. Also, I need to understand the code and have full access - so after support is essential. Especially as I need to edit this code manually myself as the information in the spreadsheet changes per supplier selection. As soon as the capability is achieved -? I will need edit all the various bits of information per supplier so I will have lots of editing of the code that I need to be able to do myself once the base capability is achieved.? I would like to learn from this(step through the code - bit by bit and need to fully understand this code). I understand bits and pieces of vba. **Easy mistakes - avoidance notes** **Blankcell note - easy mistake** Finding the first blank cell after row 4 for columns C-M population will not work because they may be valid blank cells in column C and after. If you do this there is risk that column C to M - could be populated in the wrong excel row. This is why the code need to work from the first blank cell after row 4 for column "B". Then when populating C-M remember the excel row where the first blank cell in column B was found and not find the first? blank cells it sees in in column C-M etc. **Word Document - easy mistake** Remember when populating the areas of the word doument form data could already exist. So this needs to be overritten or cleared and not have duplicate data per word document field area. If not careful you could easily end up with duplicate data. There are no book marks or anything. Also there could be form protection which might need to be considered for this when editing and overriting. **Word Document - potential easy mistake.** Due to the pasword protection complication - steping through field codes of the form is unlikely to work. Also another complication is the name and adress at the top could easily be any lenghth naturally as this is an address. There could any number of carrige returns for the address. If the code works on cariage returns its likely to fall over as it will not be able to predict the carrige returns for the address. Form lock button may also need to be considered as it could be a hindrance and little tricky especially with text fields. Also note the table part of the word forms could be filled in any number of rows so this should not affect the ability to add the applicable information in the word document. Adding infro to the table does not form part of the application. **Excel sheet - easy mistake** As I say the excel spreadsheet could be very large in terms of rows looping through each cell 1 by 1 may compromise speed. But also remember I need to be able to easily be able to edit the code as I will need to customise the spreadsheet population.