+ Reply to Thread
Results 1 to 4 of 4

Alerting other users that a workbook has been UN shared and possibly when it's been shared

  1. #1
    Registered User
    Join Date
    03-25-2016
    Location
    Seattle, Washington
    MS-Off Ver
    MS Office 2013
    Posts
    3

    Alerting other users that a workbook has been UN shared and possibly when it's been shared

    Howdy all,

    I'm running Excel 2013 and my workgroup is dead set on running in a shared workbook. I have tried to convince them otherwise but no go. I revised all of my VBA macros to deal with the shared workbook issue by either working around it when possible or making the sheet exclusive when necessary then resharing the workbook. Problem is when the macro pulls the workbook from being shared only the person making the sheet exclusive gets any warning. For instance if someone runs the script they get a warning that they are about to unshare the workbook and they can say "go for it" but once that's done anyone else in the workbook has no idea it's happened until they try to save their work, then they get a notice. I don't need to get any input from the other users and I don't need them to be able to stay in the workbook, I just want for all the users currently in the shared workbook to get a popup that alerts them they are about to get excluded.

    Here's my check for shared workbook and reestablish shared workbook, that are at the beginning and end of some of my scripts:

    'Check if the workbook is Shared, if so kill it
    If ActiveWorkbook.MultiUserEditing Then
    ActiveWorkbook.ExclusiveAccess
    SharedCheck = True
    Else
    SharedCheck = False
    End If

    'Reestablish shared workbook
    If SharedCheck = True Then
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ActiveWorkbook.Name, accessmode:=xlShared
    Application.DisplayAlerts = True
    MsgBox "Workbook has been made a Shared Workbook again"
    End If


    Appreciate any help that can be offered.

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Alerting other users that a workbook has been UN shared and possibly when it's been sh

    In your If SharedCheck = True, you have an if then for true, but not an else for false. Would adding an Else line with a msgbox do what you are wanting?
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Registered User
    Join Date
    03-25-2016
    Location
    Seattle, Washington
    MS-Off Ver
    MS Office 2013
    Posts
    3

    Re: Alerting other users that a workbook has been UN shared and possibly when it's been sh

    Naw that would give an alert for the person running the program that the workbook wasn't shared. That'd be fine but I want to alert ALL the users in the workbook that the workbook isn't shared anymore. Right now the only person that would know the workbook was 'unshared' was the person who did the unsharing.

  4. #4
    Registered User
    Join Date
    03-25-2016
    Location
    Seattle, Washington
    MS-Off Ver
    MS Office 2013
    Posts
    3

    Re: Alerting other users that a workbook has been UN shared and possibly when it's been sh

    Might have found a solution but need help with a bit of it.

    Using the ActiveWorkbook.UserStatus produces a matrix of the usernames (column 1), access time (column 2), and type of access (column 3), listing them down in rows.

    I want to take this matrix and pull each username from column 1 and put them into a string, separating each user name by ";". Is this the join command? struggling to figure out a clean count for this, but I think I'm just missing something simple.

    Appreciate any help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Multiple Users in a Shared Workbook
    By athyeh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2014, 05:38 PM
  2. MsgBox to list users in shared workbook
    By systemx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-09-2006, 11:10 AM
  3. Replies: 1
    Last Post: 01-28-2005, 09:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1