Related Posts
In which hikes are given in Accenture??
Additional Posts in Excel Genius
Time to convert to xlookup
Visual Storyteller
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Passwords by tab or else you can use access control by user/groups.
i think two versions where you hide tabs and make it read only?
Like someone said earlier, you create an Unhidden version (i usually label as INTERNAL at the end of the name) and then you can create 2 separate versions for the use case where the tabs you don’t want to share are “very hidden”.
The passwords / read only / etc options are good in theory but lots of times I don’t want end users to be able to find or access the raw data or tables my scorecards are built off of, but I DO want them to be able to use slicers.
What you do is right click on the tab you don’t want to show and click “code”. Then the VBA page will pop up but you don’t need to code anything. In the top section on the left, you should see your tabs. In the 2nd section on the left you should see that one field says “Visible”. If you hover over you’ll see a drop down and you can select “hidden” or “very hidden”. Select “very hidden”. You can repeat this across all tabs you want to hide.
If you close out and go back to the workbook, you can right click on the tabs “unhide” and it won’t show that any tabs are hidden.
I cannot tell you how many excel “gurus” are completely unaware this is even an option! Hope it works for you - it’s been great for me :)
I have a similar use case in that I have sheets for internal use but also a version of which must also be shared. I simply copy and paste the external stuff into a new file and send that out to the external groups.
I can think of no legitimate reason why we would want to combine and share one workbook and risk exposing internal info.
What I’d do is: a login page using with VBA. The steps would be something like this:
1- Create a new sheet as a Login Interface, with the option to select the “username” (in these case I’d put a dropdown option with the 2 groups identifications) and a place to write the password (if the groups aren’t allowed to see the others group infos)
2- Create a new sheet as a Login Database to have the Username info for the dropdown (remember to add an admin to have access to every thing). Also, add a table with the info of which sheet each group should have access to.
3- With VBA, make so every time you open this file all the sheets are invisible and only the Login Interface is visible.
4- With VBA create a password check for the login. It could be a if statement with a vlookup.
5- With VBA create a code to make the sheets visible accordingly to the username aka the group. In this other question I wrote a code that does this and it would be very similar in you case:
https://joinfishbowl.com/post_upwpfk7o4u
5.5- In this case, it might also help to have the sheets protected. This way, you can set which cell (and info) can be changed by the users
6- Share the password (separately) to each group member
Ooh yeah, if people are using google sheets, this might not be the best option.
They are similar solutions, but they’re not the same. So, conditional formatting, VBA, sheet protecting, … might (and most certainly will) not work as it did in the Excel desktop app.
Row level security? But I’ve never done this in excel
You don’t need a macro for this. You can just group + hide certain elements on a sheet and password protect the sheet.