How to restrict access to tabs of a worksheet for different users?

9
8450
How to restrict access to a worksheet's tabs
Excel Spreadsheets

A constant question we have received from our customers and users of our excel forum é How to restrict access to a worksheet's tabs?

We have already developed excel spreadsheets for several years now. The official dates vary between 2011 and 2013, but anyway, much water has already passed from below that bridge. We start with only 3 spreadsheets and today we have hundreds of products that help the increasingly specific and interesting needs!

However, we always wonder how to make our worksheets easier to use and more powerful. Therefore, over the years we have advanced our studies in usability and also of Excel itself, fact that made us launch our own courses.

We recently decided to venture into a new area that can completely change how our spreadsheets are used: the development of macros. These are VBA codes that allow spreadsheets to perform typically software-only functions.

A recurring request from our customers was what could create different users for a spreadsheet and that they only have access to some areas of the spreadsheet. This is an amazing feature that can be used in any spreadsheet and make the use of it more specific. So, I'll show below how we set up this macro that is now for sale on the site!

1. Login when you open the worksheet

When you use our macro in your spreadsheet, every time you open it, you will need to log in. Of course, each level of access will see different tabs open!

How to restrict access to a worksheet's tabs

2. User Creation and Levels of Access

Logging in with an administrator account, you will be able to see all the tabs of your spreadsheet, view all registered users and also the different levels of access programmed.

excel-user-registration

excel-spreadsheet-access-levels

And that's it! It's pretty simple, right? But make a big difference for anyone who is working with sensitive data like project budgets, salaries or employee performance appraisal! See our video tutorial below:

Did you like the post? Buy this one macro already ready for use of user creation and access level!

Excel Spreadsheets

9 COMMENTS

  1. Hi Rafael,

    1 - this worksheet was made to work in Excel, with the possibility of errors if it is used in Google Sheets or Excel Online - my suggestion is that you use the dropbox for people to access the file, so the use of Excel is maintained and the integrity of the worksheet as well
    2 - this spreadsheet only generates a macro to create passwords and restrictions for users - you can do what you want with your spreadsheet - increase tabs, decrease, etc.
    3 - right, access is controlled by the administrator
    4 - can edit yes, only VBA code that has restricted access

  2. Good afternoon!

    I have interest in the spreadsheet but I have some doubts ...

    1º You would need to put this spreadsheet online and so share it with some people and each person should only access the specific tab related to it.
    2º Can I create a panel, where only I have access, compiling information from the other tabs?
    3 The person who accesses the spreadsheet, it will not see other people's tabs, right?
    4 Can I edit the spreadsheet freely? Ex: create and delete tabs, users, formatting, etc.

    Thank you,

    Rafael

  3. Hi Celina, to do this you will need to use VBA code with loop and analyze each of the lines of that particular spreadsheet

  4. Hello,
    I am trying to hide the 31 row, which must be hidden according to the following condition: If it is empty that it is hidden, if it is not kept visible,

    The sheet that I am working on is associated with an updateable excel that I am updating by parameters, what I would like is that when setting the parameters, if the row 31 does not call information, it automatically hides it

    Thank you for your help !!!

  5. I am interested in a login worksheet of access divide in 4 steps, have in buy
    contact me

  6. How many levels of access can you have? I need to create a workbook with a sheet per department and each department should not be able to see each other's. I would need around 15 levels.

LEAVE AN ANSWER

Please, write your comment
Please enter your name