RemoveUser Method of Workbook Object VBA

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.



50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates

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)
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.


Leave A Comment

Related pages

sql pivot explainedadvanced excel vba tutorialexcel sum if functionquery excel with sqldata definition language commands in sqlproject dashboards in excelvba typesexecutive project dashboard template3d stacked column chart excelpivot table in excel 2007 with examplehow to create excel worksheetexcel vba coding tutorialmsgbox in vb6vba application.getsaveasfilenameadvanced pivot tables excel 2007excel vba if statementsestimation template excelvba range rowssas code tutorialremoving duplicates from excelhow to put things in alphabetical order in excelexcel vba font colorvb complete reference pdfvba input message boxsample excel macro codevba msgbox examplesvba word rangevba existsautomatically hide rows in excelsql questions and answers for experiencedmerge cells in excel 2013how to check for duplicates in excelexcel 2013 advanced filterhow to insert multiple rows in excel 2007creating pivot table in excel 2007how to unhide top rows in excelvba filedialog selecteditemsexcel remove blank rowsaccess vba close reportfile handling in vb6interactive excel dashboardtechnical interview questions and answers for freshershide unhide excelshortcut key to navigate between excel sheetsprotect formulas in excel 2007what is a pivot table excel 2010excel how to delete duplicate rowsexample of vlookup formulamultiple conditions exceloffset vlookupsas reporting tutoriallist box vbaexcel vba unprotect sheet with passwordmicro security in excel 2007how to remove blank rows in excel 2007excel macro iconexcel formulas 2007 with examples in exceldeleting rows in excelvba excel activatetutorial excel vlookupexcel hyperlink cellhow to autofit in excel 2013edit macros in exceldebug print vbavba create hyperlinkduplicate cell excelvlookup right to leftexcel spreadsheet templates for project managementexcel dynamic dashboardexcel formulas hlookup