Removing a Users access to a Shared Workbook

[Aussiebear]

I recently came across this code and was wondering if it removes a particular User or all Users of a Shared workbook?


Sub Remove_User()
Dim UsrList()
UsrList = ThisWorkbook.UserStatus
For i = 1 To UBound(UsrList)
ThisWorkbook.RemoveUser (i)
Next
End Sub

I'm assuming the Owner of the Workbook, maintains a list (UsrList) of those who can access (Read & Write) the workbook somewhere.

[Paul_Hossler]

Sounds like it

https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.removeuser?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaxl10.chm199138)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

I went to File, Share and it asks where to put it and who can read or edit it

Never really tried

[Aussiebear]

Thank you Paul.

[georgiboy]

I think, the way the code is created, it will remove all users. Reason being that 'Workbook.UserStatus' creates an array of all users currently in the workbook:
https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.userstatus

The loop then proceeds to remove every user that is in the array, therefore removing every user.

This would have the same effect as unsharing the workbook and then resharing it (this is what I do to files at my workplace)

I use shared workbooks at work with VBA, the only way to get to the VBA in a shared workbook is to unshare it, you are not able to edit/ view code while the workbook is shared.

To remove a specific user would be something like the below (Untested)

Sub Remove_User()
Dim UsrList()

UsrList = ThisWorkbook.UserStatus

For i = 1 To UBound(UsrList)
If UsrList(i, 1) = "Joe Bloggs" Then
ThisWorkbook.RemoveUser (i)
End If
Next
End Sub

Not sure if the 'UsrList(i, 1)' should be 'UsrList(i)' or 'UsrList(i, 1)' as i can't test due to all of my shared workbooks being full of users as i work for a 24 hour company.

[Aussiebear]

Thank you Georgiboy. My first impression was that it removed everyone, but a workbook needs an owner, since the current user must then become the default owner who is able to control the read & write permissions. However I wasn't sure, so didn't try and test it.

Ive seen a slightly different version of removing a user where it didn't use the user name but a position on the list.



Sub Remove_User()
Dim UsrList()

UsrList = ThisWorkbook.UserStatus

For i = 1 To UBound(UsrList)
If UsrList(i, 1) = User.2 Then
ThisWorkbook.RemoveUser (i)
End If
Next
End Sub


The disadvantage with this one is that you could potentially knock the wrong person out of the workbook if you got the position count wrong.

[Paul_Hossler]

What is "User.2" ?

[Aussiebear]

Sorry Paul but I didn't remember it as well as I should have....

The code was


Sub Remove_User1()
Dim UsrList()
UsrList = ThisWorkbook.UserStatus
If UBound(UsrList) > 1 Then
ThisWorkbook.RemoveUser (2)
End If
End Sub