Force read only / acquire write permission

Hello Excelforum!

I have a collection of macros that I share with other computers on my network. The macros is shared on a network drive accessible to both computers stored at Z:\Excel Macros\smpitaReports.xlam as an Add-in file. I am constantly updating and tweaking the macros but whoever is the first person to open their Excel gets write permission and the rest of us get read permission. I need to figure out a way to control this without changing network/filesystem permissions.

I figure the best way to do this is to make everyone open the file as read only by default and then when I need to make updates to it, run a macro to acquire write access. Can this be done in VBA? I have access to each terminal Excel so I can put in a startup macro to check for write access and then drop to read only if write access is found. I started to try to piece it together from searching the internet but I'm hitting a brickwall at the start. This is as far as I've gotten which is a failed attempt at a basic check for read only access that gives me an "Object Required" error.

Sub TakeControl()

Dim MyAddin
MyAddin = "Z:\Excel Macros\smpitaReports.xlam"
    
    If MyAddin.ReadOnly Then
        MsgBox "Read only access"
    Else
        MsgBox "Write access"
    End If
End Sub

Is there an easier way to do this? Ideas are welcome.

Thank you for your time!

Hello TMS!

Thank you for your attention to my thread! I get the error "This workbook is currently referenced by another workbook and cannot be closed" on the line

Set MyAddin = Workbooks.Open("Z:\Excel Macros\smpitaReports.xlam")

Thanks TMS!

With more research, I have decided to change my deployment method. I mostly followed the guidelines on another site, keeping the development copy on my computer and pushing a read only copy to the network share for others to use. I used the following code template:

Sub DeployAddIn()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: To deploy finished/updated add-in to a network
'               location as a read only file
    Dim strAddinDevelopmentPath As String
    Dim strAddinPublicPath As String

    'Set development and public paths
    strAddinDevelopmentPath = ThisWorkbook.Path & Application.PathSeparator
    strAddinPublicPath  = "F:\Addins" & Application.PathSeparator

    'Turn off alert regarding overwriting existing files
    Application.DisplayAlerts = False

    'Save the add-in
    With ThisWorkbook
        'Save to ensure work is okay in case of a crash
        .Save

        'Save read only copy to the network (remove read only property
        'save the file and reapply the read only status)
        On Error Resume Next
        SetAttr strAddinPublicPath & .Name, vbNormal
        On Error Goto 0
        .SaveCopyAs Filename:=strAddinPublicPath  & .Name
        SetAttr strAddinPublicPath & .Name, vbReadOnly
    End With

    'Resume alerts
    Application.DisplayAlerts = True
End Sub

Closing thread and marking as solved.