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/of...6)%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/of...ook.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