none
Button to kick other users of excel workbook RRS feed

  • Question

  • Hi There

    Is there a way I can add a button to my excel workbook to kick other users that have the file open but aren't at their computer rendering the workbook uneditable?

    Many thanks in advance

    Luke

    Monday, September 4, 2017 1:19 PM

Answers

  • It is impossible. First, we are unable to check if the user is away from their computer. Second, it is impossible to close the workbook on another computer by running some macros in our computers.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Luke Sykes Wednesday, September 6, 2017 8:06 AM
    Tuesday, September 5, 2017 9:33 AM
    Moderator
  • Hi Luke Sykes,

    it is possible.

    I understand that same workbook is used by multiple person and when some user has opened the file and go away then in that situation other user not able to access the file.

    for that situation you can add a VBA code to workbook. which will check after certain amount of time like user is working or not.

    or workbook is inactive for certain amount of time or not.

    if workbook is inactive then then you can call function which will save the workbook and close the workbook.

    below is the code example you can try to refer.

    Dim CloseTime As Date
    Sub TimeSetting()
        CloseTime = <span style="background-color: #ffff00;">Now + TimeValue("00:00:15")</span>
        On Error Resume Next
        Application.OnTime EarliestTime:=CloseTime, _
          Procedure:="SavedAndClose", Schedule:=True
    End Sub
    Sub TimeStop()
        On Error Resume Next
        Application.OnTime EarliestTime:=CloseTime, _
          Procedure:="SavedAndClose", Schedule:=False
     End Sub
    Sub SavedAndClose()
        ActiveWorkbook.Close Savechanges:=True
    End Sub
    


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Call TimeStop
    End Sub
     
    Private Sub Workbook_Open()
        Call TimeSetting
    End Sub
     
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
       Call TimeStop
       Call TimeSetting
    End Sub
    
     

    so that the workbook get close safely and other user can access it.

    to get the detail information to how to use this code you can try to refer link below.

    How to save and close workbook after inactivity for a certain amount of time?

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Luke Sykes Monday, September 11, 2017 10:42 AM
    Friday, September 8, 2017 8:06 AM
    Moderator

All replies

  • Hello Luke,

    Do you want to disconnect the users for your shared workbook? Please visit About the shared workbook feature

    You could use Workbook.RemoveUser Method (Excel) to disconnect the specified user from the shared workbook.

    Please note that we are unable to edit macro for a shared workbook, so please complete the macro locally and then share the workbook.

    If I misunderstand, please let me know and share the detail repro steps when you manually achieve your requirement.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 5, 2017 3:29 AM
    Moderator
  • Thank you Celeste

    The idea is to have a button on the sheet you can click if you're only able to open it read only because another user has it open but is away from their computer.

    The workbook is not shared in the 'Share Workbook' sense, it's just available to use for multiple workers on a shared network drive.

    Tuesday, September 5, 2017 9:23 AM
  • It is impossible. First, we are unable to check if the user is away from their computer. Second, it is impossible to close the workbook on another computer by running some macros in our computers.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Luke Sykes Wednesday, September 6, 2017 8:06 AM
    Tuesday, September 5, 2017 9:33 AM
    Moderator
  • Ok

    Thank you for confirming

    Tuesday, September 5, 2017 12:25 PM
  • You are welcome and you may use shared workbook or Excel new feature co-author to collaborate on a workbook with multiple people.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Luke Sykes Wednesday, September 6, 2017 8:06 AM
    • Unmarked as answer by Luke Sykes Wednesday, September 6, 2017 8:06 AM
    Wednesday, September 6, 2017 3:10 AM
    Moderator
  • Hi Luke Sykes,

    it is possible.

    I understand that same workbook is used by multiple person and when some user has opened the file and go away then in that situation other user not able to access the file.

    for that situation you can add a VBA code to workbook. which will check after certain amount of time like user is working or not.

    or workbook is inactive for certain amount of time or not.

    if workbook is inactive then then you can call function which will save the workbook and close the workbook.

    below is the code example you can try to refer.

    Dim CloseTime As Date
    Sub TimeSetting()
        CloseTime = <span style="background-color: #ffff00;">Now + TimeValue("00:00:15")</span>
        On Error Resume Next
        Application.OnTime EarliestTime:=CloseTime, _
          Procedure:="SavedAndClose", Schedule:=True
    End Sub
    Sub TimeStop()
        On Error Resume Next
        Application.OnTime EarliestTime:=CloseTime, _
          Procedure:="SavedAndClose", Schedule:=False
     End Sub
    Sub SavedAndClose()
        ActiveWorkbook.Close Savechanges:=True
    End Sub
    


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Call TimeStop
    End Sub
     
    Private Sub Workbook_Open()
        Call TimeSetting
    End Sub
     
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
       Call TimeStop
       Call TimeSetting
    End Sub
    
     

    so that the workbook get close safely and other user can access it.

    to get the detail information to how to use this code you can try to refer link below.

    How to save and close workbook after inactivity for a certain amount of time?

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Luke Sykes Monday, September 11, 2017 10:42 AM
    Friday, September 8, 2017 8:06 AM
    Moderator