Force Shared Linked to XLA file to be opened as Read-Only 1

[kjv1611]

Where I work, we have an "application" which is a series of Excel workbooks and a couple (different groups) shared .xla files where much of the VBA code is centralized.

Is there a way to tell any workbooks which link in the .xla file to automatically open the .xla file in read-only mode?

I'm not having issues with people modifying the xla file, as none or practically none of them have the slightest idea how to access it in the first place, and it's password-protected on top of that.

The problem is when I need to make a change, it'd be nice to be able to make the change in the middle of the day without asking everyone to close out of all attached workbooks and/or ensure I open it before anyone else does in the morning.

Thanks for any thoughts.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Steve,

I avoided this add-in "feature" or liability, by exporting my modules to a common server from which each user can access to update their application(s).

I, too, had to fight the occasional app that someone would open and then disappear, and I was shut out of updating. That's why I opted for the Export/Import method for code updates.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

Thanks for the bad news. Yeah, it has its pros and cons. I'll just stick with my current methods at least for now, then.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

In the workbook.Open method, there is a read-only argument.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

Yeah, maybe I'll play with that. Thanks.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

If I find time to make any changes, it'll be likely well in the future, I'm sure.

I had an idea going with what you said earlier, Skip.

What if I were to push the add-in out and set it up to be installed for each user, and build a piece within the add-in to automatically check for and pull in updates. Is that even possible? Or do I have to just manually push out the updated file.

Skip, if you're around, and don't mind, could you provide some more detail on how you had your system setup? Just wondering if it would simplify the mess I've got here.

Thanks for any additional thoughts from anyone.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

You could publish/push your add-in for each user to install.

I simply exported my .bas modules to a public server.
Then I sent an eMail to my users in a distribution list that contained instructions for IMPORTING the .bas file into their workbook or their PERSONAL.XLSB.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

OK, yeah, if it gets to importing the .bas files, I realize it's not a big deal, but where I work, it will be a big deal. There are probably around 5 people here who would not panic if they had to do that.

And it's not that it's that difficult or you have to be a tekie to figure it out. It's just that so many have this fear and dread of technology. LOL


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Thanks to Skip for getting my mind going on this. I ended up being able to set up the shared XLA file to recognize my username. Then I wanted to ensure that if I were hit by a bus, someone else could edit it without having to log in as me. I found some code that checks against AD Groups, and that's helping a ton along those lines. So I have 2 checks in place for now, may modify later. But with this setup, everybody else opens the XLA file as read-only.

Initial idea of how to use Username: couple of ideas posted:

https://www.mrexcel.com/forum/excel-questions/542512-force-read-only-based-user.html

For AD Groups to help account for hit by bus events:

https://stackoverflow.com/a/16739236/2088609

So now, my combined solution (for the time being), my code in the "ThisWorkbook" object of the XLA file is:
[CODE VBA]Private Sub Workbook_Open()
SetAsReadOnly
End Sub

Sub SetAsReadOnly()
' Test for PC User Name
Dim strUser As String
Dim wbRCCustom As Workbook

Set wbRCCustom = ThisWorkbook
strUser = Environ("USERNAME")
' MsgBox strUser
' Set Read only File Access for each Office's specific version
If UserIsInGroup("Domain Admins") Then
Msgbox "Current user is a Domain Administrator"
If wbRCCustom.ReadOnly Then _
wbRCCustom.ChangeFileAccess Mode:=xlReadWrite ', WritePassword:="admin"
ElseIf strUser = "MyWindowsUserName" Then
' Msgbox "User is OK, so OK to edit"
If wbRCCustom.ReadOnly Then _
wbRCCustom.ChangeFileAccess Mode:=xlReadWrite ', WritePassword:="admin"
Else ' Limit Access
If Not wbRCCustom.ReadOnly Then _
wbRCCustom.ChangeFileAccess Mode:=xlReadOnly ', WritePassword:="admin"
End If

End Sub[/CODE]

Then in a module, I just kept the original code from 2nd link above:

Public Function UserIsInGroup(GroupName As String, _
                              Optional Username As String, _
                              Optional Domain As String) As Boolean
'On Error Resume Next

' Returns TRUE if the user is in the named NT Group.

' If user name is omitted, current logged-in user's login name is assumed.
' If domain is omitted, current logged-in user's domain is assumed.
' User name can be submitted in the form 'myDomain/MyName'
'                                        (this will run slightly faster)
' Does not raise errors for unknown user.
'
' Sample Usage: UserIsInGroup( "Domain Users")

    Dim strUsername As String
    Dim objGroup    As Object
    Dim objUser     As Object
    Dim objNetwork  As Object
    
    UserIsInGroup = False
    
    If Username = "" Then
        Set objNetwork = CreateObject("WScript.Network")
        strUsername = objNetwork.UserDomain & "/" & objNetwork.Username
    Else
        strUsername = Username
    End If
    
    strUsername = Replace(strUsername, "\", "/")
    If InStr(strUsername, "/") Then
        ' No action: Domain has already been supplied in the user name
    Else
        If Domain = "" Then
            Set objNetwork = CreateObject("WScript.Network")
            Domain = objNetwork.UserDomain
        End If
        strUsername = Domain & "/" & strUsername
    End If
    
    Set objUser = GetObject("WinNT://" & strUsername & ",user")
    If objUser Is Nothing Then
        ' Insert error-handler here if you want to report an unknown user name
    Else
        For Each objGroup In objUser.Groups
            'Debug.Print objGroup.Name
            If GroupName = objGroup.Name Then
                UserIsInGroup = True
                Exit For
            End If
        Next objGroup
    End If
    
    Set objNetwork = Nothing
    Set objGroup = Nothing
    Set objUser = Nothing

End Function

The only other possibility is to allow someone to open the file as ReadWrite who is not an admin as a one-off fix. However, that would happen so rarely, that it's probably not worth bothering with. Also, there are only 3 people who know or can actively find the password for the XLA file's VBA anyway. That's me, one manager, and the other IT guy (who never has touched any of the Excel stuff from day one, and has no desire whatsoever to ever touch it). So in reality, I think this solves the problem. I've tested the code with a couple of people in a sample file. The Lord willing, tomorrow morning, I'll add it to the production code, so I don't have to be concerned with whether someone beat me to the punch opening an Excel file.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Deniall]

I had a similar issue with an XLA file a few years ago.[ ] I was debugging / extending it at the same time as people in dispersed locations were using it / finding errors in it / requesting additional features for it.[ ] I had no way of knowing who was using it.[ ] My worry was that someone in a far-flung location would be blithely using an older version that contained errors that had been corrected.

My crude, far-from-perfect "solution" was to build an "expiry date" into the add-in.[ ] The first thing the add-in did when it was loaded was check the current date.[ ] If it was a small amount beyond the expiry date a warning message would appear on the user's screen, and the add-in would continue to run after the user had acknowledged the message.[ ] If the current date was too far beyond the expiry date the add-in would refuse to run properly.[ ] Then the user was forced to apply for an updated version.

Initially, when development was fast and furious, each new release would have an expiry date no more than a week after the version's release date.[ ] As things settled down later in the project this margin was gradually increased, getting up to three months by the end.

The use of an expiry date also *discouraged* piracy of the add-in, but any half-way smart person could have found a way to circumvent that.