0
votes

I need some help regarding the "Cannot run the macro (...). The macro may not be available in this workbook or all macros may be disabled.". I have tried the most common suggestions like enabling macros & compiling the project but it does not help.

Short introduction:

I have created a macro which for years run succesfully (let's call it old version) but recently the company upgraded to Office 2016 Professional Plus and it started giving "Subscript out of range error". I have verified and corrected the macro - the reason was quite trivial: new versions of excel create 1 sheet in new workbook while the older ones created 3. The macro tried to do an action in a non existing sheet.

I have saved the changes and sent the macro for testing (new version).

The macro runs at Workbook.Open by showing a simple form, but new version does not show the form on client's computer. Simply nothing happens. I have added a command button, which starts the macro and sent it for testing, but after pressing the button there is again a "Cannot run the macro" message. I thought thet maybe there is an error in the code but after compiling and double checking everything on my PC I could not find any errors. So I have created a new workbook from the scratch with a single line macro:

Sub test() 
MsgBox "macros enabled"
End Sub

To my surprise running this macro on client's computer also returns the same error message. At this point I thought that there are some security settings that prevent running the macros but a) the old version of the macro is working, b) I have asked the client to download some free example macro from the net and it also works.

Just to add: the new version of the macro (and obviously the MsgBox macro) is working fine on my home PC (Office 2013), my work laptop (Office 2019) and my wife's work laptop (Office 365). The problem is only on client's machine.

Any suggestions will be appreciated.

TL:DR Macro is working on Office 2019, 2013 and 365 but returns error on Clients Office 2016.

1
Maybe helpful: support.office.com/en-us/article/… and if the Excel file with the macro in it is somehow downloaded from the internet or other unsecure zone you might need to ask the customer to first unblock the file.rene
Thanks, but the macros are enabled and the one from the net is working fine. The ones I share dont.mishaT
Sounds like the client's Office install might need to be repaired. Are they running only one version of Office? Or are they e.g. running 2016 but then later installed Access 2010?Mathieu Guindon
You shouldn't rely on "new versions of excel create 1 sheet in new workbook while the older ones created 3". This is a user setting - my new workbooks have been set to 1 worksheet for as long as I remember.Comintern
@rene that +10 answer (now +9) is terrible, unjustified advice. Popping a MsgBox doesn't require enabling VBA code to write & modify VBA code as it's running. Don't tick that checkbox unless you're actually doing meta-prorgamming in VBA, or need to run code that does. I can't believe the votes on that answer, the OP (both here & there) has nothing to do whatsoever with the VBIDE API. I wish I could downvote that answer thrice.Mathieu Guindon

1 Answers

0
votes

During a meeting, when I had a chance to test it myself, it turned out that the macro was deleted by their mail server antivirus. So @rene was right to suspect it. Thank you all for help.