Unanswered: Creating individual logins for mulitple worksheets (VBA),
I have a report that goes out to multiple reps. I have a piece of code that pulls the data down via an ODBC driver, and then breaks out the individual detail by rep into seperate worksheets, and labels each worksheet with the reps name.
Now, I want to password secure each worksheet so that they are not tempted to look at each other's information.
I have a column in the main data pull that has a unique employee # that I would like to use as the password.
Do you guys have any idea how I would go about getting this done. I assume I would create a form of some kind to prompt the user to enter their employee #, and I obviously want to hide the main worksheet.
I really need the code to do some sort of lookup from the main worksheet to derive employee # and possibly name as the report will change month by month, and I can't put static rep names into the code.
Would be happy to buy someone a cold beer in exchange for some help!
I would recommend that you amend your current code so that it breaks the individual detail into different distribution workbooks. Each distribution workbook should only contain data that the relevant rep should be able to see (this sounds like it would be one worksheet per workbook). A clever user would be able to bypass any security you impose within the workbook so hidden or protected data would not be safe.
Our reps are not that smart, but they are well paid. So, I'm not worried about them cracking the code. Most cannot operate excel to begin with...
I have to keep it in one workbook because I will be posting the workbook out to our sharepoint shared drive for them to access. I don't want to have to post individual workbooks for each rep as we have about 100.