This is an update on a requirement I posted a few weeks ago (sorry to the people that bidded – I limited it to Sydney based, and did not get a suitable applicant response, so the limitation of a Sydney based programmer has been revised and I am posting more detail on the requirements.
The key requirement is for someone to neaten up the front end, file linking and improve code security. I believe should take an experienced programmer no longer than 15-20 hours of work. (Most of what is being asked is available in Excel VBA forums)
The role is flexible and we will be looking for someone who is able expand and to fully utilise the VBA capability of Excel 2007. Knowledge of VBA manipulation of pivot table and charts would be a distinct advantage. I will e-mail more details to the successful candidate. There is likely to be more follow up work for the successful candidate.
The main sheet will be in [url removed, login to view], so this is the first file that is opened.
On opening the main file:
• On opening this file show a pop-up screen (a form with our logo on it, stating the conditions of use of the software) the person needs to click “I accept” for the file to load, otherwise it will close the file and exit.
• Show a form which indicates the links that the file has to other files (basically the data links on the Data>Edit Links path in Excel 2007).
• Once the user confirms the linkages, that the linked files are then opened in the background as well.
• Modify the user environment so that the file opens in Full Screen view – there should be no excel options showing other than a customised menu which should be accessible from all screens. The list of menu items should reflect the items in the Menu List screenshot.
• All coding to reside in [url removed, login to view] – even for the other files so that there is only one location for coding for the solution.
• Error handling – the code should have error handling functionality (ie if the code fails an error log text file is created)
• The environment should be in a “Secure” state at all times when used by a normal user. That means that:
o Only unprotected cells should be editable. (Only cells with a light yellow background should not be protected – in the attached sandpit file)
o Sheet tabs are to be hidden and not visible.
o Hidden rows or columns cannot be unhidden without permission.
• Pivot tables features (this is the key coding requirement, and this is the most important aspect of the coding) See attached pic [url removed, login to view]
o [url removed, login to view] has a set of days and a pivot table showing skills etc (Days are the ReportFilter Items)
o [url removed, login to view] has a set of days, skills & tasks (Days are also ReportFilter Items which are the same as the range in in [url removed, login to view])
o These are both in pivot tables and I want to be able to select the combination of available Dates for the pivit tables to be filtered on from a form with drop down boxes so that when I change the ReportFilter Date in [url removed, login to view] it will also change the field it automatically change the matching RosterSource pivot table to also filter on that day.
• All links should be relative to the main file location, so that all linked files need to be in the same location (folder location) to be visible to the main file.
• That all VBA project code is obviously password protected.
• Close and save all the files
• Re-establish the excel environment (get the standard ribbon back, headings visible etc)