1

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

Mahesh Uddama
  • 31
  • 1
  • 9
  • 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ᴇʜ Jun 12 '19 at 14:22
  • Macros run, if the source file of macros opened. which happened automatically before. now it become manual. – Mahesh Uddama Jun 12 '19 at 14:35

3 Answers3

1

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://learn.microsoft.com/en-us/deployoffice/overview-of-update-channels-for-office-365-proplus#visual-representation-of-the-update-channels-for-office-365-proplus

Mahesh Uddama
  • 31
  • 1
  • 9
0

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

Alvaro CC
  • 142
  • 6
  • added some screen shots, confirms the macros are fine and executable when the source file is opened. but previously if the source file is not opened, first it opens in background and execute. seems it loose some level of permission with the update – Mahesh Uddama Jun 12 '19 at 17:37
0

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • no, normal workbooks are not macro enabled, so i cannot place vba codes there. custom toolbar tries directly to access macros. keep remember that it worked before for more than 2 years. – Mahesh Uddama Jun 13 '19 at 08:28
  • If your code runs from a custom toolbar you should consider placing it into the PERSONAL.XLSB to make that working: See [Copy your macros to a Personal Macro Workbook](https://support.office.com/en-us/article/copy-your-macros-to-a-personal-macro-workbook-aa439b90-f836-4381-97f0-6e4c3f5ee566?ui=en-US&rs=en-US&ad=US) • I assume you had the `master.xlsm` auto loading on Excel's start in your previous installation without rememering that. – Pᴇʜ Jun 13 '19 at 08:36
  • this approach to make sure macros visible to all workbooks every open excel, i get that. in this way i cannot export the custom toolbar and work in a different pc as it needs to manually transfer all macro to the new pc and place in roaming folder and change all custom toolbar macro path to new user's roaming folder. this was the reason the master file was in c:\master folder so all users of this toolbar and macro could adopt it easily. – Mahesh Uddama Jun 13 '19 at 09:20
  • @MaheshUddama then you should probably create an add-in or use [this thechnique](https://stackoverflow.com/questions/8850836/how-to-add-a-custom-ribbon-tab-using-vba) – Pᴇʜ Jun 14 '19 at 07:38
  • in a worst case i will try, right now im expecting to find a fix for the difference made in a recent update, probably a security exception, registry edit or policy change. because it worked for long time even with office 365 starting from office 2013. – Mahesh Uddama Jun 14 '19 at 10:57