Macros from another workbook stopped working after office update

1
votes

I have a excel workbook with macros (master.xlsm) and these macros can be accessed by other workbooks via custom toolbar in excel. previously when a normal workbook opens and access one of these macros in the custom toolbar,

  1. if master.xlsm not opened yet, then it opens in the background and execute the macro to the normal workbook
  2. if master.xlsm already opened, then execute macro directly to the normal workbook.

now with a recent office 365 update, method (1) is blocked. therefore i have to manually open the master.xlsm to execute the macros.

now in (1) it shows,

[cannot run the macro "C:\Main\master.xlsm'!macro1" macro may be not avaiable or all macros may be disabled]

Tried in a different PC, with windows 10 upto date and office 365 upto date. it works, and the built version of office 365 in the working pc is MSO(16.0.11328....) and the faulty pc is MSO(16.0.11727....) this can be an obvious reason, but both versions office 365 is upto date.

both PCs have same macro security levels and master.xlsm location has added as a trusted location in excel trust center locations.

also an update ago faulty PC also worked so all files and settings are identical.

my question has excel vba security criteria changed? or how to solve without downgrading office built version.

below image 1 when master.xlsm not opened so it gives the error image 2 when master.xlsm is opened so it execute macro and insert text to the book1.xlsx

when master xlsm not opened

when master xlsm is opened

3
excelvbaoffice365
First of all test if macros run at all. Add a test Sub into that file and let it throw a message box for example MsgBox "I run!" and run the procedure. If you don't see the box there is a macro security issue. If you see the box there is issue with your file or code. - Pᴇʜ
Macros run, if the source file of macros opened. which happened automatically before. now it become manual. - Mahesh Uddama

3 Answers

1
votes

after all Microsoft accepted that this is an issue in Office 365 Monthly and Monthly (targeted) channels, and they have fixed it already in Monthly targeted channel and waiting for the Monthly channel fix. apparently semi annual and annual channels were not affected by this issue in the first place because of they bring the most stable updates from Monthly channels to the semi annual and annual channels. i think its good to choose semi annual or annual update channel if you are small business and not eagerly waiting for every single update immediately every month.

Answer https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/macros-from-another-workbook-stopped-working-after/9779bd5f-e858-4213-9ea6-95597e867a07?messageId=4d2e3ce7-350d-4bfd-82b2-f54c8654016e&page=1

Overview of update channels https://docs.microsoft.com/en-us/deployoffice/overview-of-update-channels-for-office-365-proplus#visual-representation-of-the-update-channels-for-office-365-proplus

0
votes

Well , I would start by checking whether "open" macros works in "master.xlsm" something like this:

Thisworkbook_open

This will not probably fix your problem , but could show some light on where the problem is

Also posting here some of the code in master.xlsm would help

0
votes

Well you can only run a macro in master.xlsm when it is opened. If it is closed you cannot run the macro in it, you need to open it first. Eg use the Workbooks.Open method to open it first.

Dim MasterWb As Workbook

On Error Resume Next 'test if master.xlsm is already open
Set MasterWb = Workbooks("master.xlsm")
On Error Goto 0

If MasterWb Is Nothing Then 'if it was closed open/run/close it
    Set MasterWb = Workbooks.Open("C:\Main\master.xlsm")
    Application.Run "master.xlsm!macro1"
    MasterWb.Close SaveChanges:=False
Else 'if it was already opened just run macro and don't close it (or you might loose changes that were already made)
    Application.Run "master.xlsm!macro1"
End If