0

I have a VBA add-in that sits in a shared file drive that three users in my organization have added to their Excel. I thought the add-in would be handy because I could push changes to the code without updating the code in any individual Excel sheets. When I tried updating the code in the add-in, the add-in was read-only because other users had Excel open. I've been trying to figure out how to make the add-in read only whenever anyone else (meaning anyone other than me) opens an instance of Excel. I've added the below code to a class module in my add-in. I've tried debugging, but no part of the below code is executed when a user opens a new workbook. I'm totally stuck.

Private WithEvents App As Excel.Application

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

    If App.UserName <> "Andrew Lubrino" Then

        SetAttr "C:\FilePath\Add-in.xlam", vbReadOnly
    
    End If

End Sub

I've found a number of posts on this topic all with accepted code for seemingly the same problem, but the code simply doesn't work in my environment. Can anyone tell me what's going wrong?

edit:

I'm trying to make the add-in read only for all other users so that when I try to edit and make changes to the distributed code, I can save those changes. With the current set up, I can't save the code whenever any other user has Excel open. For the above code to work, you'd need the event to trigger for any workbook that another user opens

Andrew Lubrino
  • 67
  • 1
  • 6
  • I'm trying to make the add-in read only for all other users so that when I try to edit and make changes to the distributed code, I can save those changes. With the current set up, I can't save the code whenever any other user has Excel open. For the above code to work, you'd need the event to trigger for **any** workbook that another user opens. I'll leave an edit in my original post with all this. – Andrew Lubrino Jun 10 '22 at 17:59
  • You cannot change the attributes to a open file. Then, who will bother them to change this attribute and open the add-in with macro disable, then modify the code? – FaneDuru Jun 10 '22 at 18:01
  • Sorry, I'm having some trouble understanding. You mention "change this attribute". What is an attribute in this case? I think you're saying that if I do make any changes to code, users will have to close and reopen Excel anyway for those changes to take effect. That's true, but at least I won't need them to close all at the same time so that I can make edits. – Andrew Lubrino Jun 10 '22 at 18:04
  • `ReadOnly` is the attribute you try changing in code. Which is not possible... And no need to any class to make the add-in `Workbook_Open` event being triggered. – FaneDuru Jun 10 '22 at 18:06
  • Even if it is ReadOnly (from the beginning, or in code as you need) any change done by you in such a case will not be active for the ones having the add-in open (in ReadOnly mode)... You can un-share the add-in, modify it and then announce the colleagues to close Excel and reopen it. – FaneDuru Jun 10 '22 at 18:09
  • Okay, so basically this method doesn't work. Is there another way to make an Excel add-in read only for everyone but me? – Andrew Lubrino Jun 10 '22 at 18:10
  • No, it is not, but anyhow, like I tried explaining before, it will be useless... – FaneDuru Jun 10 '22 at 18:11
  • Yes, I know that. After I'm finished making edits, I could email them with something like "please restart Excel for changes to take effect." – Andrew Lubrino Jun 10 '22 at 18:11
  • Okay, so there is really no solution for making changes to a distributed add-in short of asking everyone to close out while I make the changes? – Andrew Lubrino Jun 10 '22 at 18:13
  • You can place a kind a marker in a text file, where all loaded add-ins to read a value. Let us say 1 or 0. Before using a Sub/Function the code will check the marker. If it is 1, they will receive a message box, saying to restart the add-in. You can programmatically close it. – FaneDuru Jun 10 '22 at 18:13
  • Ah you mentioned un-sharing the add-in. I missed that. Thanks! – Andrew Lubrino Jun 10 '22 at 18:14
  • If the folder location of the add-in is read-only to everyone but you (or anyone else who needs write access) then users cannot lock the add-in when they load it, so you can replace it at any time (but they'd need to reload it to see any changes). If you don't want that then set the "read-only" flag on the file, edit a copy, then replace it with the edited copy. – Tim Williams Jun 10 '22 at 18:40
  • Here is an answer of mine addressing this issue: https://stackoverflow.com/a/50474925/8597922 by using two copies: one development (for you) and one production (for users). Option described in this answer still works, however I have since moved onto a separate file that is responsible for assembling the add-in from scratch and saving it onto the drive. – Victor K Jun 10 '22 at 19:11

0 Answers0