News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Online/Multi User access with Excel running Macros

    Posted on WSBRoby Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Online/Multi User access with Excel running Macros

    Viewing 8 reply threads
    • Author
      Posts
      • #2137261 Reply
        WSBRoby
        AskWoody Lounger

        Hi Everyone,

        Is it possible to use a macro enabled spreadsheet from Onedrive? I am using Office 365 on Windows 10 Pro.

        I have a spreadsheet that I am using for a roster system. I have set it up so that users can select their name from a drop list in each “shift” cell. Once they select their name, a macro runs, triggered by the worksheet change event, to “lock” the cell. i.e. the first person to nominate for a shift, can lock in for the shift.

        If a person needs to be removed from a “Shift”, a supervisor can “Unlock” the cell and remove the name, thus leaving the cell available for someone else to nominate.

        So far, from what I have read, and from my testing, it appears there is no way to allow Excel to be used with macros enabled and functional in an online environment such as OneDrive.

        Any suggestions on what I could do to get around this “No Macro” rule in an online world?

        TIA

        Brian.

      • #2137266 Reply
        Kirsty
        Da Boss

        Is it possible to use a macro enabled spreadsheet from Onedrive? I am using Office 365 on Windows 10 Pro.

        It appears you should be able to, between these articles from Microsoft Support:
        Enable or disable macros in Office files: (Office 365 Subscription, Office Online)
        Work with VBA macros in Excel for the web: (Excel for the web)

        Note that you can USE, not CREATE, macros, though!

      • #2137550 Reply
        wsbroby
        Guest

        Hi Kirsty,

        Thanks for your reply.

        I have looked at those articles and the second link provided does specifically state “Although you can’t create, RUN, or edit VBA…” etc…

        It does rather appear that I am at an impass, even though the macros are “left alone” when using Excel Online, they are not operational. πŸ™

        Another team member came up with a solution using Google Sheets, this program, while not allowing the power of what can be achieved with Excel, does allow multi user, online access with users having the ability to insert comments in cells that can then be resolved by the management team.

        Looks like we will be stuck with that solution, which is not a bad solution at all, just that some functionality that I had in Excel will not be available in Google Sheets.

        Thanks
        Brian.

      • #2137863 Reply
        RetiredGeek
        Guest

        Brian,

        I just ran a test with this macro:

        Option Explicit
        
        Sub Auto_Open()
        
        Dim zName As String
        Dim iAge  As Integer
        
        zName = InputBox("What is your name?", "Name Entry")
        iAge = InputBox("How old are you?", "Age Entry")
        [A1] = zName
        [B1] = iAge
        End Sub
        

        I stored the file on my OneDrive and then exited Excel.
        After the initial run where I had to enable macros, note it could be setup as a trusted document or location, it started up automatically the next time I opened it.

        Now I do have an Office 365 subscription and I did open it up on my computer where that subscription lives (not on my phone)! So if your users are accessing Excel from their computers with 365 subscriptions you should be able to run your macros from OneDrive stored .xlsm files.

        The only other problem would be if two users tried to access it at the same time but this could be handled by testing for Read Only access in the Auto_Open macro and providing a message to exit and try again shortly.

        HTH 😎

        P.S. Didn’t catch I wasn’t signed in…RetiredGeek

        • #2137879 Reply
          RetiredGeek
          AskWoody MVP

          Brian,

          Upon further testing I was able to open the OneDrive workbook on another computer using Excel 2010 after setting up my OneDrive folder as a trusted location.

          However, when opened it did NOT show up as read only meaning that each machine has it’s own copy of the workbook and if two open it up it at the same time (which I did) only the last one to save it will have their changes recorded!

          I’ll have to ruminate on this a little longer.

          HTH 😎

          May the Forces of good computing be with you!

          RG

          PowerShell & VBA Rule!
          Computer Specs

        • #2137901 Reply
          RetiredGeek
          AskWoody MVP

          Brian,

          Ok, this works unless you get some really weird timing, which I doubt, where two users open the file at almost the exact same moment.

          Option Explicit
          
          Sub Auto_Open()
          
          Dim zName As String
          Dim iAge  As Integer
          
           If ActiveSheet.ProtectContents Then
             MsgBox "This workbook is currently in use." & vbCrLf & _
                    "Please try again later", vbOKOnly, _
                    "Workbook in use:"
           Else
               UserProtect
               ActiveWorkbook.Save
             
              zName = InputBox("What is your name?", "Name Entry")
              iAge = InputBox("How old are you?", "Age Entry")
            
              UserUnprotect
            
              [A1] = zName
              [B1] = iAge
            
              ActiveWorkbook.Save
            End If
            
            Application.Quit
          
          End Sub
          
          
          '                           +------------------+                  +----------+
          '---------------------------|    UserProtect   |------------------| 11/05/98 |
          '                           +------------------+                  +----------+
          
          Sub UserProtect()
          
              If Not ActiveSheet.ProtectContents Then _
                ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
                                    Scenarios:=True, userinterfaceonly:=True
              If Not ActiveWorkbook.ProtectStructure Then _
                ActiveWorkbook.Protect Structure:=True, Windows:=True
              
          End Sub                      'UserProtect
            
           
          '                            +------------------+                 +----------+
          '----------------------------|   UserUnProtect  |-----------------| 11/05/98 |
          '                            +------------------+                 +----------+
          
          Sub UserUnprotect()
          
              If ActiveWorkbook.ProtectStructure Then _
                ActiveWorkbook.Protect Structure:=False, Windows:=False
              If ActiveSheet.ProtectContents Then _
                ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
                                    Scenarios:=False, userinterfaceonly:=False
                 
          End Sub                      'UserUnProtect
          
          

          The process is to lock the worksheet and SAVE the file immediately upon opening of the first user.
          Then any subsequent user will get a message box as shown below.
          When the user completes their input (which all must be gathered while the sheet is locked, i.e. in your macro) the sheet us unlocked and the file saved and then excel is closed automatically.

          Message to subsequent users when file is in use (locked):
          ExcelOneDrive

          When user presses OK Excel is automatically closed.
          HTH 😎

          May the Forces of good computing be with you!

          RG

          PowerShell & VBA Rule!
          Computer Specs

          Attachments:
          1 user thanked author for this post.
      • #2138173 Reply
        WSBRoby
        Guest

        Hi RG,

        Nice to see you are still on the boards.

        As I cannot verify that all staff have an installed version of Excel>=2010 I find that I am having to stay with the Google Sheets option.

        I do love your solution though.

        I would also find that I would have to travel to virtually everyones system to setup this process, somthing I am sure would probably not be the preferred option as staff and volunteers are spread over a large geographical area.

        I did note that someone mentioned that it was desirable to have their timesheets filled in automatically from the roster system and since I have already figured out how to import the Google Sheets data, using a Data Query, I think that will be a good change to implement in this project.

        As for the original desire to operate Excel online… I think that aspect is dead πŸ™

        Thank you for your valuable time and effort in bringing your solution to the table.

        Cheers

        Brian.

      • #2138904 Reply
        RetiredGeek
        AskWoody MVP

        I would also find that I would have to travel to virtually everyones system to setup this process, somthing I am sure would probably not be the preferred option as staff and volunteers are spread over a large geographical area.

        Brian,

        You should look into PowerShell. Via PowerShell Remoting you can do a lot of work on remote machines as long as they are on the same network. It’s a great tool that has a bunch of power and potential.

        HTH 😎

        May the Forces of good computing be with you!

        RG

        PowerShell & VBA Rule!
        Computer Specs

      • #2138915 Reply
        anonymous
        Guest

        Hi RG,

        I am dealing with a small number of paid staff and a large number of Volunteers.

        So it is all home PC’s not on a network.

        I am familiar with remote desktop control (such as Teamviewer) but at this point, I am now happy to stay with the Google Sheets solution, as it involves the least amount of user impact.

        Thanks for your help anyway.

        Brian.

      • #2141529 Reply
        zeddy
        AskWoody_MVP

        Hi Brian

        If your issue is with the small number of paid staff, stop paying them and turn them into volunteers.

        If your issue is with the volunteers, then pay them and turn them into paid staff.

        zeddy

        • This reply was modified 1 month, 2 weeks ago by zeddy.
      • #2141748 Reply
        anonymous
        Guest

        Hi Zeddy,

        Somehow, that is just not going to happen πŸ™‚

        Have worked out what needs to happen, now just getting everything lined up.

        Cheers

        Brian.

    Viewing 8 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Online/Multi User access with Excel running Macros

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.