OnAction / Cannot run the macro 'x' - The macro may not be available in this workbook or all macros may be disabled

[relaxok]

Hi there - I'm the guy responsible for all the VBA in our workplace for the last decade or so.

All of a sudden, presumably due to a forced Windows or Office update, myself and everyone else are getting the error in the subject above, in one particular case that it's never happened before.

Cannot run the macro ''Macros.xlsm'!'UTILITY_HideOrShow "8111","8151'', The macro may not be available in this workbook or all macros may be disabled'
(example error)

More specifically the macro is triggered when clicking one of many 'expand' buttons on a table of contents type page - the macro is UTILITY_HideOrShow - a public sub in a public module and it takes 2 parameters for the beginning and end row to hide/unhide. It is in a separate .xlsm document and for the last 8-10 years has worked like this. The button's macro is assigned like so:

With targetCell
    Set btn = ws.Buttons.Add(.Left, .Top, .Width, .Height)
    btn.text = "+"
    OnActionString = "'UTILITY_HideOrShow """ & CStr(startRow + 1) & """,""" & CStr(endRow) & "'"
    btn.OnAction = OnActionString
End With

The table of contents page is generated by a nightly process - so I tried changing things around such that the hide or show macro was in the generated workbook itself, and not the separate 'macro' workbook. However, I get a version of the same error when doing that.

I also tried having OnAction associated with a simple test macro without parameters (in case parameter were an issue), both in the macros doc and in the workbook i'm generating - and got the same error. it's almost like OnAction on buttons is no longer able to 'find' any macros. It knows which macro the workbook is in because the error message says the name of the macro workbook.

I also noticed that the quoting looks odd, in that the 2nd parameter has no double quote at the end but it was working like that all this time. I tried messing around with that to no avail and as I said above, tried removing parameters altogether.

Does anybody have any advice here?

Did anything change with a recent Excel update that requires changes from me in settings or the workbook itself?

Are you guys able to trigger macros with OnAction on a button still?

This is Excel version 2110 in Office 365.

Thanks!

 

[rlv01]

The Microsoft release notes for 2110 don't indicate anything.

Release notes for Current Channel releases - Office release notes

Provides IT Pros with release notes for Monthly Channel releases for Microsoft 365 Apps.

docs.microsoft.com

1. Have you checked the Trust Center security settings on your new install?

2. Have you gone back to basics?

Sub ButtonTest()
    Dim btn As Button
    Set btn = ActiveSheet.Buttons.Add(10, 10, 50, 20)
    btn.Text = "+"
    OnActionString = "HW"
    btn.OnAction = OnActionString
End Sub

Sub HW()
  Debug.Print "Hello World!"
End Sub

Will the macros run if you call them another way, say by using and ActiveX command button instead of a form button?

 

[Fluff]

Goto macro security settings & check the box marked "enable Excel 4.0 macros" & see if that works.

 

[relaxok]

Goto macro security settings & check the box marked "enable Excel 4.0 macros" & see if that works.

YES, thank you so much!

God that's so strange, how are they Excel 4.0 macros? What determines that?

Thank you to all that responded.

 

[Fluff]

You maybe using one of the old Excel4macro functions in your code.