I’m working with Office 2016 Excel.
I have a workbook that contains 11 Sheets. There is a Main Roster Sheet, which is the holder of all the information. This information is imported in from an outside source and contains information that feeds 9 of the 11 sheets. HR is the only one that should have access to this sheet.
Then I have 9 regional offices that need to have their own sheet. Their worksheets will look up the employees’ name from the Main Roster sheet, and bring over the information that is allowed to be seen by employees of the 9 individual regions. Let’s say the sheets are the source data sheets, and are called “Dog”, “Cat”, “Bird”, “Snake”, “Gerbil”, “Pig”, “Horse”, “Fish”, “Pony.”
Then I have 1 sheet, Current Roster, which basically counts the number of employees from each regional sheet that falls into certain criteria levels.
Everyone in the company should have visibility into the “Current Roster” Sheet, so that they are able to see the #’s. However, each region should only be able to see the their own regional source data sheet. And then HR should be able to see All sheets.
I’m trying to write code that:
When the workbook is opened, the only sheet that is visible (unhidden) is the “Current Roster” sheet. If someone from the Dog region wants to see their source data sheet, then I want them to be able to unhide that work sheet.
I watched a few videos and it looks like there is a way to put a rectangle on the Current Roster worksheet, and assign it a macro-that will in turn allow the user to enter a password to have the sheet unhidden. This is what I want to do for each region. I want them to have their own button, so that when they enter the password then that sheet will be visible. And once they close the workbook, I want the sheet to revert back to hidden.
Can anyone help me with the code I would need to make this happen? I’ve attached a powerpoint that shows screen shots of what I want, and some screen shots of the videos I’ve watched (but I keep getting errors)…
And I may not be explaining it very well either:(. I basically need all the worksheets in a workbook hidden (except Current Staffing), and those worksheets to only be able to be unhidden IF the password is known for that individual worksheet. So, each worksheet will need to have a separate password (except Current Staffing, which will always be visible).
Thank you!!!!!!!
Please see Topic “Hide Multiple Sheets and Require unique pw to unhide” – double posting
