How To Disable "Notify" Button When Accessing an Already Opened Workbook

[Flash2017]

I have an Excel workbook on a server that multiple users look at for reference but only one person is allowed to edit. I am trying to keep other users from opening the file and selecting the "Notify" option on the prompt. We have had issues where people would select the "Notify" option and would take control of the actual file itself without realizing it, making it impossible to save changes to it.

I have attempted some VBA coding trying to curtail this but this prompt appears to run before the VBA code can take effect.

I have looked in multiple places for an answer to this and haven't found anyone with a true solution to this exact issue I am having. The closest thing I have found to what I am looking for is this thread from 2005: https://www.mrexcel.com/forum/excel-questions/169316-how-disable-notify-button.html

So, I am looking for some insight to somehow disable the notify button or load straight into the "Read Only" version of the file without the prompt appearing at all.

Any help with this would be greatly appreciated!

 

[Trevor_S]

If the other users are accidentally (rather than deliberately) opening with edit access, why not set the file properties as read only? Navigate to the file in file explorer, right click, select properties and tick read only. Other users will automatically be given a read only version, without the option to notify, etc.

When the user who is allowed to edit wants to open the file, they can do so from file explorer, unticking the item in the properties window first.

 

[Flash2017]

Thanks for the reply Trevor_S! I have given some thought to doing this before posting. However, the only issue I have with setting the file to read only is that whenever it comes times to edit the file, it rehashes the issue. During the time window where the file is being edited, the other users who try to access it may hit the "Notify" button and take control of the file as soon as it is closed on the editor's computer. The other issue is that this document is being edited frequently throughout the day so this issue would persist because the file is periodically closed and reopened.

I considered password locking the modify option, but the users were adamantly opposed to this idea. I have also tried to save it as "Read-only recommended" file. This did exactly what I was wanting but it still prompts the user if someone is editing the file if they would like to open the read only version or be notified when the file becomes available. Ugh.

I can manually remove people from the document from the server end by kicking them off the file when it occurs, but it is frustrating how frequently I have to do this.

 

[Trevor_S]

Ok, another option ... have two copies of the file. One "editable" copy that only those who are allowed to edit the file can access. That copy can have a save event macro that also saves a copy of the file as read only recommended to the current location that everyone can access.

That way, no other users will access the editable copy, so it will never be locked when you need it. Those opening the read only recommended copy will always get a read only version without the "notify" option, as no one will ever have it open for editing.

 

[Kentetsu]

The route I usually take in a situation like this is to give them a dummy workbook with a button. They click that button and it opens the target spreadsheet as Read Only. Pretty simple to setup, and works like a charm...

 

[Flash2017]

I am trying to implement this solution. However, I am having an issue with the code. I found this code from VBAExpress.com that I have modified to work with this but it isn't working properly. It seems to create a file on the desktop with a random name. A windows pops up saying "Be careful! PArts of your document may include personal information that can't be removed by the Document Inspector." Then it immediately disappears after you click okay. I have little experience with VBA and I'm not sure how to mediate this issue.

This is the code that is producing this issue:

Sub Readonly()
    SaveAsReadOnly Macro
    Dim copyname As String
    copyname = "VBAMultiplierTest (Reference Version)"
    ChDir "C:\Users\myName\Desktop"
    ActiveWorkbook.SaveCopyAs Filename:= _
    "C:\Users\myName\Desktop\Spreads & copyname.xlsm"
    SetAttr "C:\Users\myName\Desktop\Spreads & dt.xlsm", vbReadOnly
End Sub

and I have also tried this code but this one didn't seem to do anything

Sub SaveFiles()

    Const PathWorkList As String = "C:\Users\myName\Desktop\VBAMultiplierTest.xlsm"
    Const PathWorkListReadOnly As String = "C:\Users\myName\Desktop\VBAMultiplierTest (Reference Version).xlsm"
   
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=PathWorkList
    
    On Error Resume Next
    VBA.SetAttr PathWorkListReadOnly, vbNormal
    
    ActiveWorkbook.SaveAs Filename:=PathWorkListReadOnly
    ActiveWorkbook.SaveAs Filename:=PathWorkList
    
    VBA.SetAttr PathWorkListReadOnly, vbReadOnly
    
    Application.DisplayAlerts = True
 
End Sub

I would appreciate any advice that you could give me with this. I can't really figure out where it is going wrong.

 

[Jaafar Tribak]

This could probably be achieved using a hook or a timer in an addin that would continiously check for the opening of the specific workbook and disable the Notify button in the dialogbox .

A different workaround is to have code in the opening event of the workbook that checks if the latter is ReadOnly and if so close it and reopen it on the fly with the Notify argument set to False.

Something like this :

Private Sub Workbook_Open()

    Application.OnTime Now, Me.CodeName & ".Actual_On_Open"
    If Me.ReadOnly Then
        Me.Saved = True
        Application.ScreenUpdating = False
        Workbooks.Open Filename:=Me.FullName, ReadOnly:=True, Notify:=False
        Kill Me.FullName
    End If

End Sub

Private Sub Actual_On_Open()
[COLOR=#008000][B]    'Any Pre-Existing Opening code goes here...[/B][/COLOR]
End Sub