VBA not running on a Shared and Protected workbook?

[pete4monc]

Hi guys.
I have just spent days getting a spreadsheet ready to track our file circulation and now have hit a bloody wall.
I have had to protect some cells so there is no fiddling and the spreadsheet needs some vba to run to move details from one department (sheet) into the next department(sheet).
All works absolutely fine until I share the spreadsheet?
Is it not possible to share an protected workbook with vba? Sorry I'm pretty new to vba so any help would be greatly appreciated.
I must say that in my vba only the protected cells "unprotect" when data is being transferred, is this the issue?

Thanks in advance.
Pete

 

[Joe4]

If you do a Google search on "excel vba not working on shared workbook" and "excel protection not working in shared workbook", you will find many posts that discuss the issues that "shared workbooks" have with these features (i.e. if you are using/sharing the workbook via "Excel Online", that does not allow to run VBA and you cannot change protection in a shared workbook.

In general, it seems the suggestion is to avoid using "Shared Workbooks", and use other alternatives, such as co-authoring (I have never done this myself, but I don't have to deal with shared workbooks).

Here is another discussion that is a few years old, but talks about some of the limitations and alternatives: Avoiding Shared Workbooks in Excel - Contextures Blog

 

[pete4monc]

Hi @Joe4
We are sharing the excel spreadsheet over our network so unfortunately I have had to relinquish the protected cells but thanks for your input.
I'll do some more googling in case I come over something. Thanks again!!

 

[Joe4]

Here is an interesting article on Co-Authoring: Everything You Need to Know About Co-Authoring in Excel

 

[pete4monc]

Hi @Joe4
That looks fab! Only downside only works on Excel 365. I have 365 at home but I don't think the boss would outlay the money to upgrade....lol
I have saved that page to my favourites for future. Thanks again.

 

[Joe4]

You are welcome.