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

excel vba print commandexcel project plan timeline templatemultiple project tracking template excelwhat is concatenate in excelvba excel accessunprotect excel vbaunprotect excel vba codevba loop untilvba multiple if statementscreate pivot chart excel 2010remove duplicates in excel 2010excel checkboxselect rows vbacheckbox tutorialswot analysis template powerpointconditional statement in vbscriptifcount excelnew adodb.connectionuse of vlookup in excel 2010excel formula insert rowexcel vba offset functionmicrosoft office gantt chart templateexcel vba goto labelvba create objectproject status report ppt templatevba macro for loopequals vbaexcel vba chartexcel clear cellvlookup function tutorialhow to change width of column in excel 2007vba data entryexcel graphs examplesvba excel pivot tableado odbc connection stringbubble charts in excelunprotect password excelconvert xl to xmlsheets excel vbahow to insert a dropdown in excelvba filter criteriaexcel construction templatesunhide excel rowcombo boxes in excelvba unprotect workbookclear listboxchart wizard excelexcel 2007 listboxrecordset excel vbaloop vba codeexcel graph titlecharter template project managementexcel startup macromicrosoft excel vba referenceexcel in cell charttrim formula in excel 2007how to delete odd rows in excelwhat is sumif functionexcel chart objecthow to unprotect excel sheet forgot passwordvba break statementexcel hide sheetsvba clearcontentswhat does append mean in accesssql tutorial for beginners with examples pptvba excel pivot tablelogical formulas in excel 2007 with examplestrend analysis formula in excelhow to unprotect an excel workbook 2010excel vba write to text fileexcel input form templateexcel coding basicsoperators and operandsformula in vba excelexcel calculate weeks between two datesremove duplicate entries excelhow to delete columns in excelaccess msgboxhow to unlock the excel fileexcel macro while loop