Hide Multiple Sheets and Require unique pw to unhide

Topic

New Reply

Hello! I am working on a spreadsheet (Office 2016) and need some VBA help. I tried watching videos, but it didn’t quite work. This workbook needs to be accessed by several regions- but each region should only be able to see their own source sheet, and only the bigwigs should be able to see all the regions’ sheets along with the master sheet. So, on the one sheet (Current Staffing) that everyone is able to view, I have created buttons. I want to hide all the other sheets, and then if “Dog” region goes in and wants to view their source data, I want them to be able to click their button, which will open a password prompt- which they will enter their region unique password, and then the “Dog” worksheet will be visible to them. When they exit the workbook, I want to make sure that all the sheets revert back to hidden. So, like I said, I’ve inserted a drawing to act as my button. I know that I will need to assign a macro to each of those buttons…but I’m not sure what vba language to use. Is anyone able to…well, basically, give me the code I’d need for one of the buttons? And then I figure that I could replicate the code for the other buttons.

I would like my button to pull up a password prompt that tells the user to enter the password. If the password is correct, I wan the prompt to disappear and the worksheet to be unhidden. If the password is incorrect, I want a message to tell them “Password Incorrect. Retry?” Yes…then allow to try to reenter, No…then close prompt and keep sheet hidden, but allow them to still see the “current staffing” tab.  I’m attaching a ppt with screen shots of my ‘dummy’ file. Thank you in advance for any and all help!!!!!!

 

Please see Topic “Struggling with Code” – double posting.

Reply | Quote

Replies

Can’t you do it via individual sheets for each region and then a master to read the external sheets? Saves macros that may be problematic and makes restoring from mistakes easy.

You can save the region sheets in separate directories that each region has permission to and users of the master sheet has permission to the region directories too. Easier than managing multiple access and passwords.

cheers, Paul

1 user thanked author for this post.

jabeattyauditor

Reply | Quote

Hi Paul

I’m not sure what you mean. The Masters sheet (Main Roster) is maintained by HR and feeds the data to the individuals regional sheets, but only the information that is allowed to be known by the individuals in the regions. The master sheet contains more sensitive data. But one region is not privy to another region’s data. While the columns are the same, that data is unique and needs to be protected from another region seeing. And then ALL regions need to be able to see the “current staffing” sheet. So I need to hide all the regional sheets and the master sheet, and make it so that each sheet is only able to be unhidden to whoever is allowed to see that particular sheet. So the bigwigs want to have the regions have their own password to each sheet.

I  need to know how to hide a sheet and then have it only unhidden by a password. If I glcan get the coding down for one sheet, then I can just replicate the process for the others and change the password for each.

I wonder if I should post the question in the VBA forum?

 

Thank you!

Reply | Quote

I was suggesting individual sheets for all regions and a master to consolidate the lot.

Having a “current staffing” sheet is more difficult. Maybe export one sheet to a location all regions can see it.

I seem to remember Retiredgeek or maybe zeddy providing something like that. Might be worth waiting a little longer to see if they pop in.

cheers, Paul

Reply | Quote

New topic refers to this same query:
Struggling with Code

Please note the Lounge Rules regarding cross-posting and duplication, which are designed to stop others from giving you information they don’t realise you have already been given!

Reply | Quote

I don’t know if it is encouraging to say that doing this could be difficult.  You may want to hire a gig worker to do it on a dummy spreadsheet without any private data.  VBA in Excel is a pretty complete programming language, so little is impossible, but it is well known that “Excel is not a database.”  Businesses that had a lot of resources would usually try to keep anyone not specially trained away from directly entering data into a spreadsheet, to prevent them from corrupting it.  A GUI or form, with users, data validation, etc would be used for input.

As a middle ground, doing what Paul T suggests and having each region access only their own spreadsheet as a separate file in an access protected directory would lower the chance of mistakes.  Or the separate files could each use a different password with the standard Excel password feature.  A master sheet file could be run on an account with greater permissions to combine the data, or that high-permission account could know all passwords and (batch) convert the passworded files to unpassworded ones, kept in restricted directories, and a report builder could export the data that needs to go back to each region.

Reply | Quote

Hi

In the attached zip file, extract the workbook to a folder of your choice.
Load it into Excel with macros-enabled.

NOTE: the vba project has NOT been password-protected, to allow you to see the code.
You would add a password to the vba project before deploying any such workbook.

Excel experts would always be able to gain access to a protected vba project.
Such protection is only designed to prevent casual access by general Users.

For simplicity, we can us a single routine assigned to ALL the Region Buttons on the sheet.
This common routine checks which button was clicked by checking the clicked-button’s text.
For simplicity, we can colour-code the Region sheet-tabs to make it easier to hide/display.
We could use other methods to control access to sheets.
In this attached example, I have not added ANY worksheet or workbook protection.

I added a simple routine to prevent Users adding new sheets to the workbook
(other than those with ‘developer’ access).
I also added a simple routine to prevent changes to the workbook being made outside office hours.
It would be simple to remove these restrictions etc etc etc

If a User has given a valid ‘developer’ password to ‘unlock’ the workbook, then they
can click on the Region buttons without having to enter a password for that particular Region.
All Region worksheets are visible, and this allows quick movement between sheets etc etc etc.

Passwords for access to sheets are defined on a very-hidden [Parameters] worksheet.

Admin/IT Support

John Smith  3333  MANAGER
Fred Jones  4444  MANAGER
Amanda Williams  5555  MANAGER
Julie Wright  6666  MANAGER
Richard Wilson  zeddy  Developer
Daniel Johnson  barbados Developer

The [Parameters] worksheet is visible to Developers only.
They can set and manage access passwords on this sheet etc etc etc

Region Worksheets are listed on the [Parameters] sheet, together with access codes..

Code:

Region sheetname Access Code
Dog   denmark
Cat   canada
Bird   bhutan
Tiger   tuvalu
Car   china
Bike   brazil
Drum   denmark
Tower   turkey
Walrus   west indies
Main Roster  malta

Provision is made for temporary access to unlock any Region worksheet..
This allows us to give any User a temp pwd that is valid up to
a specified cut-off time for today’s-date-only for a specified Region worksheet..

the temp pwd is given as a string of 9-or-more mixed chars/symbols that
follow a specific rule:
first 4 chars: anything
5th and 6th char: first-and-last-letter of sheetname for that button
7th and 8th char: 2-digit day-of-current-month ; e.g 7th Feb => 07
9th char: single digit 1 to 9 = number of hours after 9:00am pwd is valid till
e.g 9th char = 7 = 9:00am +7 = 4:00pm = pwd expires after this cut-off time
if 9th char is NOT a digit then NO timit has been set for today’s use
10th char + :these chars are ignored

check for temporary Manager access..
This allows us to give any Manager a temp pwd that is valid up to
a specified cut-off time for today’s-date-only..

the temp pwd is given as a string of 9-or-more mixed chars/symbols that
follow a specific rule:
first 4 chars: anything
5th char: first-letter of today-day-of-week; M=Monday; W=Wednesday etc etc
6th char: first-letter of current month; F=Feb; N=Nov; D=Dec ;  etc etc
7th and 8th char: 2-digit day-of-current-month ; e.g 7th Feb => 07
9th char: single digit 1 to 9 = number of hours after 9:00am pwd is valid till
e.g 9th char = 7 = 9:00am +7 = 4:00pm = pwd expires after this cut-off time
if 9th char is NOT a digit then NO timit has been set for today’s use
10th char + :these chars are ignored

garcich-zeddy-v1

zeddy

1 user thanked author for this post.

Paul T

Reply | Quote

Hi

Further to my previous (long) post, I should say that I agree with Paul T in that it would probably be better to have separate Excel workbooks for each of the Regions.

The original post said that only bigwigs should see all the regions’ sheets, so I assumed these were Managers of some sort. It is not clear how the Excel file is ‘distributed’ e.g. via email, via placement in controlled server-folder-locations etc.

In my experience, such Managers sometimes have ‘deputies’ that occasionally cover their work, so rather than the Managers giving out their personal access-code for the file, I made provision for IT-support to give a ‘time-sensitive-pwd’ that would allow temporary access for a given day only. For the same reason, a temporary pwd could be emailed to another User to give time-limited access to a specific Region sheet. For example (using rule given in previous post):

4L2kTm177R-!p : this would give Manager access up to 4pm on Tue March 17th

zPQ3fM203$J#9 : this would give Manager access up to midday on Fri March 20th

A different rule is used for giving temp access to a particular Region worksheet.

You can create your own rules for IT to use and amend the vba accordingly etc etc etc.

zeddy

Reply | Quote

From my own experience, I would discourage you from relying excessively on the password protection in Excel sheets and tabs.  I claim no great technical ability or skill, but was quite disconcerted at how easily I was able to find, and then execute, a hack on a Excel (XLSX) file that I downloaded.

So – do go ahead as you outline further up, but don’t be too alarmed if someone misuses the information that you thought that you had safely hidden behind your framework of passwords.

Dell E5570 Latitude, Intel Core i5 6440@2.60 GHz, 8.00 GB - Win 10 Pro

Reply | Quote

Garcich,

When I was working I developed some workbooks along the lines you described. Here is the approach I took.

• The organization was LAN based so I depended on the LAN permissions for the security of the data.
• This was in a training organization so it was based on Courses.
• Each course had a workbook (all course workbooks had the EXACT same layout).
• Each course director had access only to their course workbook based on LAN permissions.
• Group chiefs had access to all the workbooks for the courses they were responsible for, again using LAN permissions.
• Group chiefs had a “roll-up workbook” which had macros to import the individual course workbooks and “roll-up” totals to a summary sheet.
• Division chiefs had a “roll-up workbook” which had macros to import the individual group workbooks for all the Groups they were responsible for and “roll-up” totals to a summary sheet while still being able to see the individual group numbers.
• Finally, the Director of Training could summarize the Division numbers while still being able to see individual Groups and Courses information.
• This was all controlled by macros (VBA Code) so it took no technical knowledge on the part of the users to operate the entire system and it required absolutely no passwords other than their already assigned level of LAN access and associated logon password.
• Of course the VBA Projects (Macros) were hidden and password protected just to keep wannabe tweakers out of the works!

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote