RemoveUser Workbook is used to remove access to the user from the shared workbook. We are using RemoveUser method of workbook object to remove access from the shared workbook.
Why we need to RemoveUser from the Shared Workbook?
Sometimes we need to removeuser from the shared workbook access. For example an employee has moved from the organization that time we may need to remove access to employee form shared workbook access.
VBA RemoveUser Workbook – Syntax
Here is the syntax to remove user from the shared workbook using VBA.
Workbooks(“Your Workbook Name”).RemoveUser(Index As Long)
Where Index is the mandatory argument. It represents the index of user to remove access from the shared workbook.
In the above syntax we are using ‘RemoveUser’ method of workbook object to remove user to access the workbook.
VBA RemoveUser Workbook:Example 1
Please find the below example, It will show you how to remove user from the shared workbook.
Sub Remove_User1() Dim UsrList() UsrList = ThisWorkbook.UserStatus If UBound(UsrList) > 1 Then ThisWorkbook.RemoveUser (2) End If End Sub
Explanation: In the above example we are removing the second user from the available user access list of workbook.
VBA RemoveUser Workbook:Example 2
Sub Remove_User2() Dim UsrList() UsrList = ThisWorkbook.UserStatus For i = 1 To UBound(UsrList) ThisWorkbook.RemoveUser (i) Next End Sub
Explanation: In the above example we are removing all the available user access from the workbook.
Please follow the below steps to execute the VBA code to save the excel file.
Step 1: Open any existing Excel workbook.
Step 2: Press Alt+F11 – This will open the VBA Editor.
Step 3: Insert a code module from then insert menu.
Step 4: Copy the above code and paste in the code module which have inserted in the above step.
Step 5: Now removed users cannot access to the shared workbook.
Step 6: Now press F5 to execute the code.
Hey! Join Our Community
Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!