Online/Multi User access with Excel running Macros

Topic

New Reply

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.

Reply | Quote

Replies

WSBRoby wrote:

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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):

When user presses OK Excel is automatically closed.
HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

1 user thanked author for this post.

Kirsty

Reply | Quote

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.

Reply | Quote

WSBRoby wrote:

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

Reply | Quote

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.

Reply | Quote

[zeddy]

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 5 years, 2 months ago by zeddy.

Reply | Quote

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.

Reply | Quote