jxm461
03-16-2010, 10:42 AM
Ok, looking for a bit of help here.
I have a form in Excel that i need to send out to managers and have them complete. The form has all sorts of lookups and formulas imbedded such that when you enter the employee name in cell C7, all the other demographic and performance data populates. What i want to do is insert code in the workbook so that:
It runs through the Roster list (separate worksheet) and for every manager in the "Reports To" field, it inserts the employee name in cell C7 (thereby populating the rest of the worksheet).
Then it inserts a blank copy of the form into a new tab and does the same population until there's a change in the "Reports To" field, at which time it saves the workbook by the manager's name (Reports To field) and prompts me for a folder in which to save the files and creates a new workbook for the next manager and does the same.
I was hoping to lock out these fields using the Sheet Protection options prior to running the code so that one could not even select that field (thereby viewing other employee performance data).
Does anyone know how i could accomplish this?
Ideally I would have liked to just create a form in Access and have all the managers use it to input data but there are about 100 managers using it and it would likely be too big of a change in process to implement for them. People are resistant to change, particularly at such a large scale.
I have a form in Excel that i need to send out to managers and have them complete. The form has all sorts of lookups and formulas imbedded such that when you enter the employee name in cell C7, all the other demographic and performance data populates. What i want to do is insert code in the workbook so that:
It runs through the Roster list (separate worksheet) and for every manager in the "Reports To" field, it inserts the employee name in cell C7 (thereby populating the rest of the worksheet).
Then it inserts a blank copy of the form into a new tab and does the same population until there's a change in the "Reports To" field, at which time it saves the workbook by the manager's name (Reports To field) and prompts me for a folder in which to save the files and creates a new workbook for the next manager and does the same.
I was hoping to lock out these fields using the Sheet Protection options prior to running the code so that one could not even select that field (thereby viewing other employee performance data).
Does anyone know how i could accomplish this?
Ideally I would have liked to just create a form in Access and have all the managers use it to input data but there are about 100 managers using it and it would likely be too big of a change in process to implement for them. People are resistant to change, particularly at such a large scale.