Excel tutorials

How to Limit Multiple Workbook Users to Access a Specific Worksheet

By reading a variety of formulas and following the websites that are related to Excel, I came across a very interesting topic that is related to limiting certain users when they work on the same workbook. In this Excel example, I have elaborated on how many more users can work in a workbook with certain permissions and limitations, with regard to the use of specific worksheets. All credit goes to the authors of the workbook that is published by logit.

One Workbook Multiple Users and Different Passwords

We can imagine the situation as follows. You work as an administrator in a company that has a shared office and a computer in it. Workers work in the field with clients. When they return to the company, at the end of their working hours they use one computer to enter data after work has been done in a common office. The Excel workbook is located on the office computer used by multiple users. You as an administrator need to set permissions, limit access to a specific workbook for each worker, you have to allow some workers to enter data on certain worksheets, and some worksheets can only be read and viewed. The workbook consists of several worksheets. In the picture below, find out what a workbook looks like in this example.

Worksheet 'SetUp'
This worksheet can only be accessed by Administrator. In this worksheet, Admin sets key parameters for accessing specific worksheets as well as some items for the visual appearance of popup messages. In the 'A3:C12' cell range, the administrator can modify the text, as well as a password to remove the protection on other worksheets in the 'K12' cell. This password should only be known to the administrator.

SetUp worksheet for setting and restricting multiple users of the workbook

 

Worksheet 'Intro'
This worksheet can be accessed by all users and it is input worksheet.

Form for logging in and using Excel workbook

Other worksheets are used for the work of users where they enter the required data. You can delete the 'ReadMe' worksheet, but you do not have to. If you delete it then you must remove it from the list in the 'SetUp' worksheet. Below, find out how other worksheets look like.

Allowed permissions and access rights to the relevant Worksheet

 

Instructions for working on same a Workbook that has limited access rights for different users

Before you start working on this workbook, you must be able to allow 'Execute VBA macros' in your Excel program. Of course, be careful who you trust and where is the source of a workbook that has the extension (format) *.xls or *.xlsm.
This file, which I gave this tutorial, is *.xlsm format and can be used in Excel 2007 and later. If you want to use this workbook in Excel 2003 then you need to use the *.xls file.

Before you customize this spreadsheet Excel spreadsheet for your own use, I suggest that you try a few times and that simulate names for your needs, to become familiar with the way it works this workbook.

The pop-up message frames used in this workbook can be customized to a certain degree, for example, to include a company name, etc. Use the cells in the 'C' column on the "SetUp" worksheet.

You set the user rights and permissions to the settings in the "SetUp" worksheet. Put the letter "X" in the cell for each worksheet that the user can access and enter the data.

Put the letter "R" in the user table that will be able to access the specific worksheet, but the worksheet will be locked and the user concerned will not be able to make any changes to the data on it. A user who has placed the letter "R" can only read and watch the respective worksheet.

The permissions and user rights on the "SetUp" worksheet are displayed as a network of 11 x 10, but there are no user list limitations.

If you add a user to the 'N' column and if you add a new worksheet to the list, Excel will automatically detect the number of users/worksheets. So, if you want to add a new user, just add in the next column of the table, the new user name and set his password and authority (ie rights and permissions). Also, if you want to remove someone from the list, simply remove his name. Here I want to point out that in 16th row the cell range 'N16: IV16' there must be no text or data input because it will be displayed in the drop-down list when the user's 'Log In', on the 'Intro' worksheet. After adding new users or new worksheets, click the "Populate List" button.

The sheets SetUp and Intro must be present in this workbook and not renamed. All other tabs in the workbook may be deleted (including this one).

If in this workbook, you add your own worksheets you're already working on, it is recommended that you do this by copying workbooks from your original workbook to this workbook. You can do this by using Edit => Move or copy sheet (in the original workbook).

The "Intro" Workbook will be available to all users, regardless of whether their profile is set to "SetUp". This is the only worksheet that will be seen if the user decides to disable VBA macros when starting. On this worksheet is set VBA button for access ( 'Log In').

User passwords are placed below username (row 16), but I advise you to format these cells so that the content in them is not visible. They will appear in the formula field (Formula Bar), at the top of the working window if the cell selected is in this row. In this case, I have deliberately set the 16th row password to be visible. Of course you can set them invisible by setting the user format for all cells in row 16. See this link how to Hide Content of Cell in Excel.()

If you want to use and share this workbook, the author claims there is no reason why this should not work as a shared workbook over the network (Sharing).

The VBA code in VBE is currently unprotected. To provide greater security in this workbook as an administrator, set the password for accessing the VBE, which will not allow access to unwanted persons. Do not forget the respective password.

Be sure to back up your workbook before you start working. Keep in mind that this workbook and worksheets are not 100% safe and protected from unauthorized and malicious people. Remember! There is no 100% protection and will never be.
Any Excel protection can remove the experienced and advanced Excel user. There can be only lighter or heavier protection for someone who wants to "hack". Protecting a Workbook in Excel and Protection of the Worksheet in Excel. This Workbook has protection from the average Excel users and is sufficient for work on an average level. If you need a high level of security and protection then do not use Excel.

Thanks to Logit, Erik Van Geit and Nasser Fathy I have shown you this Excel tutorial and I hope to be able to serve you in your daily work. Remark! Every time you click on the 'Save' recording command, Excel Vas returns to the beginning, ie it hides all worksheets and needs to be re-logged. If you want, you can add your own VBA macros for specific actions, hiding or editing permissions, and so on.

Sheet1 (SetUp)
Only 'Admin' can be accessed in this worksheet. You may not change the name of this worksheet because it is related to VBA macros.

Sheet2 (Intro)
In this worksheet can be accessed by all users. This is the Input Worksheet when a workbook is opened. It has a 'Log In' button that launches 'Log In Form'. The name of the worksheet must not change because it is linked to VBA macros.

LogIn form in Excel

 

If the password entry is correct then popup message will appear to resume otherwise, a message will appear that access is denied.

Access is allowed

 

Access denied because of invalid password

Set password and permission to access a specific worksheet for multiple users via VBA

Below, you can see the list of Modules currently active in VBE for Excel.

Modules in Excel VBE

Sheet3 (ReadMe)
This worksheet can be accessed by users who have access permissions set by Admin on the 'SetUp' worksheet. This Worksheet is not required and you can freely delete or set it up and periodically add some notices and the like.

Sheet4 (Sheet1)
With regard to the permissions and rights set by Admin on this worksheet, the following rights are allowed for the following users: (see table in the 'SetUp' worksheet)
Admin (permission to enter and change data)
Larry (permission to enter and change data)
Barry (permission to enter and change data)
Jill (permission to enter and change data)
Garry (Read-only and review)

Sheet5 (Sheet2)
With regard to the permissions and rights set by Admin on this worksheet, the following rights are allowed for the following users: (see table in the 'SetUp' worksheet)
Admin (permission to enter and change data)
Larry (permission to enter and change data)
Barry (permission to enter and change data)
Guy (permission to enter and change data)
Harry (Read-only and review)

Sheet 6 (Sheet3)
With regard to the permissions and rights set by Admin on this worksheet, the following rights are allowed for the following users: (see table in the 'SetUp' worksheet)
Admin (permission to enter and change data)
Larry (permission to enter and change data)
Pete (permission to enter and change data)
Harry (Read-only and review)

Sheet7 (Sheet4)
With regard to the permissions and rights set by Admin on this worksheet, the following rights are allowed for the following users: (see table in the 'SetUp' worksheet)
Admin (permission to enter and change data)
Larry (permission to enter and change data)
Garry (permission to enter and change data)
Barry (permission to enter and change data)
Harry (permission to enter and change data)
Jim (permission to enter and change data)
Tom (permission to enter and change data)
Jill (permission to enter and change data)
Guy (permission to enter and change data)
Pete (permission to enter and change data)

Sheet8 (Sheet5)
With regard to the permissions and rights set by Admin on this worksheet, the following rights are allowed for the following users: (see table in the 'SetUp' worksheet)
Sue (permission to enter and change data)
Tom (Read-only and review)

The following VBA macros are set in this Workbook. Press the Alt+F11 keys to access in the VBE for Excel.

Sheet1 (SetUp) Module

Private Sub CommandButton1_Click()
Dim WkSht As Worksheet, Counter As Integer
Counter = 17 'count to the last filled cell in column A
For Each WkSht In Worksheets
Cells(Counter, 2).Value = WkSht.Name
Counter = Counter + 1
Next WkSht
End Sub

Sheet2 (Intro) Module

'If you want to prevent the visibility of this procedure through the ALT+F8 shortcut,
'add the word Private at the beginning of the procedure name (before Sub)
Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("SetUp").Visible = xlSheetVisible
Pass = Sheets("SetUp").Range("K12").Value 'password from the K12 cell on the relevant Sheet
Sheets("SetUp").Visible = xlSheetVeryHidden
For Each WSht In ActiveWorkbook.Worksheets
Sheets(WSht.Name).Unprotect Password:=Pass
Next WSht
'Sheets("Intro").Protect Password:=Pass 'lock the worksheet if the user clicks on Exit to LogIn form
Application.ScreenUpdating = True
UserForm1.Show
End Sub

Thisworkbook Module

Option Explicit
Dim WSht As Worksheet, Pass As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call HideAll
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call HideAll
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("SetUp").Visible = xlSheetVisible
Pass = Sheets("SetUp").Range("K12").Value
Sheets("SetUp").Visible = xlSheetVeryHidden
For Each WSht In ActiveWorkbook.Worksheets
Sheets(WSht.Name).Unprotect Password:=Pass
Next WSht
Call HideAll
UserForm1.Show
'Sheets("Intro").Protect Password:=Pass 'Lock worksheet to start
End Sub

Private Sub HideAll()
Application.ScreenUpdating = False
On Error Resume Next
For Each WSht In ActiveWorkbook.Worksheets
Sheets(WSht.Name).Unprotect Password:=Sheets(Sheets("SetUp").Cells(30, 11).Value)
If WSht.Name <> "Intro" Then WSht.Visible = xlSheetVeryHidden
Next WSht
Application.ScreenUpdating = True
End Sub

UserForm1 Module

Option Explicit
Dim HFD As Integer, HFR As Integer
Dim N As Long, F As Long, Pass As String

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("SetUp").Visible = xlSheetVisible
For N = 3 To HFR
If ComboBox1.Value = Sheets("SetUp").Cells(15, N).Value Then
Exit For
End If
Next N
If TextBox1.Value = Sheets("SetUp").Cells(16, N).Value Then
Sheets("SetUp").Visible = xlSheetVeryHidden
MsgBox Range("SetUp!C10").Value, , Range("SetUp!C9").Value & " " & Sheets("SetUp").Cells(15, N).Value
Unload UserForm1
Sheets("SetUp").Visible = xlSheetVisible
Pass = Sheets("SetUp").Range("K12").Value
Sheets("SetUp").Visible = xlSheetVeryHidden
For F = 17 To HFD
If UCase(Sheets("SetUp").Cells(F, N).Value) = "X" Then
Sheets(Sheets("SetUp").Cells(F, 2).Value).Visible = xlSheetVisible
End If
If UCase(Sheets("SetUp").Cells(F, N).Value) = "R" Then
Sheets(Sheets("SetUp").Cells(F, 2).Value).Visible = xlSheetVisible
Sheets(Sheets("SetUp").Cells(F, 2).Value).Protect Password:=Pass
End If
Next F
Else
MsgBox Range("SetUp!C6").Value, , Range("SetUp!C7").Value
TextBox1.Value = ""
Sheets("SetUp").Visible = xlSheetVeryHidden
End If
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub

Private Sub UserForm_Initialize()
Dim WkSht As Worksheet
Application.ScreenUpdating = False
For Each WkSht In Worksheets
If Not WkSht.Name = "Intro" Then WkSht.Visible = xlSheetVeryHidden
Next WkSht
Sheets("SetUp").Visible = xlSheetVisible
HFD = Sheets("SetUp").Range("B65536").End(xlUp).Row
HFR = Sheets("SetUp").Range("IV15").End(xlToLeft).Column
UserForm1.Caption = Range("SetUp!C3").Value
Label3.Caption = Range("SetUp!C4").Value
For N = 3 To HFR
With ComboBox1
.AddItem Sheets("SetUp").Cells(15, N).Value
End With
Next N
Sheets("SetUp").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

And at the end of this tutorial here you can Download a Workbook (format *.xlsm) on your computer or Download a Workbook (format *.xlsb). Be careful, watch who you trust when you launch XLS or XLSM Excel files on your computer!

If you want to run the Excel file format *.xls or *.xlsm and you are unsure whether the file contains the malicious VBA code, then press the SHIFT key when opening the Workbook (In this way you will prevent the execution of VBA macros in start).