Macros in shared workbooks

Resolved · Medium Priority · Version 2013

Tue 15 Nov 2016, 10:10 Delegate Daniel said...

Daniel has attended:
Excel VBA Introduction course
Excel VBA Intermediate course

Macros in shared workbooks

Hi,

I am trying to run a macro in a shared workbook and it's not working. Is this because macros will NEVER work in shared workbooks? Or do certain options needs to be changed for the macro to work? When I select shared workbook, a message pops up saying that the macro won't be editable, but I didn't take this to mean it wouldn't work...
When I try to run it I get run-time error 1004 (application-defined or object-defined error).
FYI The macro protects the active sheet, greens the active tab, brings up a msg box and sends an automatic email. On a non-shared workbook the macro works fine and produces all intended outcomes, but in the shared workbook it doesn't at all.

Thank you

Wed 16 Nov 2016, 10:50 Trainer Doug said...

RE: Macros in shared workbooks

Hi Daniel

Thanks for your question.

As you say, macros cannot be viewed or edited in a Shared workbook. But they can still be run.

The only issue is that shared workbooks disable many of the standard Excel features (inserting charts, conditional formatting, inserting PivotTables). If your macro includes any of the disabled features it will cause an error.

I've created a simpler macro than yours. It changes the tab colour of the active sheet and displays a Message box is allowed.

After making the file a shared workbook changing the tab colour is disabled. So when the macro is run an error is caused. Displaying the messagebox is allowed.

There may be a work around that allows a macro to run.
Adding Activeworkbook.ExclusiveAccess at the beginning of the macro and supressing the alerts.

This does allow the macro to run. But the workbook is no longer a Shared workbook.

Here's my example

Sub TestMessage()

Application.DisplayAlerts = False
ActiveWorkbook.ExclusiveAccess
ActiveSheet.Tab.Color = 5287936
MsgBox "Email will be sent"
Application.DisplayAlerts = True

End Sub

So what would be needed now is a command to make the file a shared workbook again.

I will add to this reply if a solution is found.
I hope this helps in some way with resolving your question.

Regards
Doug
STL

Wed 16 Nov 2016, 17:05 Delegate Daniel said...

RE: Macros in shared workbooks

Thanks very much Doug. The automatic email and message box macros work in the shared workbook. The tab colouring is not that important so I will leave that out. But is there any way to get the sheet protection macro to work in a shared workbook?
Thank you

Mon 28 Nov 2016, 10:48 Trainer Doug said...

RE: Macros in shared workbooks

Hi Daniel
Sorry for not getting back earlier.

It seems that you can run macros in a shared workbook but the part where you make the file a shared workbook has to be done manually.

You would need to have 2 macros. Run macro a_ then manually share the workbook. Then run macro b_.

For example

Sub a_ProtectSheet()
Application.DisplayAlerts = False
ActiveWorkbook.ExclusiveAccess
ActiveSheet.Protect
Application.DisplayAlerts = True
End Sub

Sub b_Msg()

MsgBox "Email will be sent"
End Sub

If I hear of a better work around I'll reply again.

Regards
Doug
STL