Cannot run the macro XXX. The macro may not be available or all macros may be disabled.

Question

I get this error on macros assigned to shape objects.

"Cannot run the macro XXX. The macro may not be available or all macros may be disabled."

Issue only started happening 3 days ago... on files working fine previously.

Macros and content is enabled - Macros triggered off other events are running as per normal, all custom functions work.

The assigned macros to the shapes run fine when run via VBA editor

Issue seems to go away when the document is saved under a different name and is re-opened, but reoccurs when sent to others or re-labelled a second time or re-downloaded from source.

The issue is only occurring on computers running Office 365 Business edition.

Setting the new "Enable Excel 4.0 macros when VBA macros are enabled" check under Macro Settings to TRUE resolves the issue.

If it is related to 4.0 Macros - how can I identify these and remove them?

Example of one of the impacted macros is

Sub Add_Employment()

ActiveSheet.unprotect "Sh7Snls"

a = Int(Right(ActiveSheet.Name, 1)) 

i = 0 

Do Until Sheet3.Range("A209").Offset(0, i).Value = "SoP " & a 

    i = i + 1 

Loop 

End\_row = Sheet3.Range("A209").Offset(4, i).Value - 2 

Copy\_Rows = End\_row - 13 & ":" & End\_row - 1 

rows(Copy\_Rows).Copy 

rows(End\_row & ":" & End\_row).Select 

Selection.Insert Shift:=xlDown 

ActiveWindow.SmallScroll Down:=3 

End\_row = End\_row + 8 

Call set\_btn\_names 

Range("F" & End\_row & ":F" & End\_row + 4).ClearContents

Range("G" & End\_row & ":G" & End\_row + 4).ClearContents 

Range("H" & End\_row & ":H" & End\_row + 4).ClearContents 

Range("I" & End\_row & ":I" & End\_row + 4).ClearContents

Range("J" & End\_row & ":J" & End\_row + 4).ClearContents

Range("M" & End\_row).ClearContents                     

Range("D" & End\_row + 1).ClearContents                  

Range("F" & End\_row).Select 

Call set\_chkbox\_refs 

If Range("D11").Value = "" Then 

    Range("D" & End\_row).Value = Range("D10").Value  'Party 

    Range("D" & End\_row + 1).Select 

Else 

    Range("D" & End\_row).Select 

End If 

ActiveSheet.Protect "Sh7Snls", DrawingObjects:=False, Contents:=True, Scenarios:=False

End Sub

Sub set_chkbox_refs()

Dim chk As CheckBox

For Each chk In ActiveSheet.CheckBoxes

With chk 

    .LinkedCell = .TopLeftCell.Address 

End With 

Next chk

End Sub

Sub set_btn_names()

On Error GoTo SkipShp

a = Int(Right(ActiveSheet.Name, 1)) 

For Each Shp In ActiveSheet.Shapes 

    If Shp.TopLeftCell.Column = 14 Then 

        rw = Shp.TopLeftCell.Row 

        If rw < Range("z10").Value Then 

            Shp.Name = "SE\_Sensitisation\_" & rw 

        End If 

    ElseIf Shp.TopLeftCell.Column = 10 Then 

        rw = Shp.TopLeftCell.Row 

        PREV\_ROW = Int(Right(Shp.Name, Len(Shp.Name) - 8)) 

        Shp.Name = "InvProp\_" & rw 

        i = 0 

        Do Until Sheet3.Range("A436").Offset(0, i).Value = "" 

            If Sheet3.Range("A436").Offset(0, i).Value = "SoP " & a And Sheet3.Range("A436").Offset(1, i).Value = PREV\_ROW Then 

                Sheet3.Range("A436").Offset(1, i).Value = rw 

            End If 

            i = i + 1 

        Loop 

SkipShp:

    End If 

Next Shp 

End Sub

Answer 1

For any one that is interested I found the issue.

With the new updates (auto disable excel 4.0 macros) excel seem to no-longer support passing variables into assigned macros

example:

'MacroName "parameter 1","parameter 2","parameter 3"'

Removing these macro assignments corrected the issue.

I worked an alternate way to pass variables using the application.caller and some named ranges.

Not sure why this has a glitch - and as per my post if you saved the file an reopened it worked... but if you renamed the file it broke again... strange.

Answer 2

Just a further suggestion because you indicated "The assigned macros to the shapes run fine when run via VBA editor".

Is it necessary to run from shapes or could you maybe create ActiveX control buttons and run from them? If you create ActiveX buttons then you could either move the code to the required worksheet module and run directly from the ActiveX button or use the ActiveX button to call the existing code.

Answer 3

You might also like to refer to the following articles relating to the issue which I have found since my previous reply. However, if you don't have any Excel 4.0 macros then it appears that there is a glitch in the system.

https://techcommunity.microsoft.com/t5/excel-blog/restrict-usage-of-excel-4-0-xlm-macros-with-new-macro-settings/ba-p/2528450 

https://docs.microsoft.com/en-us/deployoffice/admincenter/overview-security-policy-advisor#:~:text=The%20following%20are%20the%20requirements%20for%20using%20Security,365%20Apps%20for%20enterprise.%20...%20More%20items...%20

Answer 4

Thanks OssieMac,

I ran the script and there were no Exce4Sheets

I have also sent the feedback form as suggested.

If others have suggestions please let me know.

Answer 5

First up you could try running the code by Allen Wyatt at the following link to establish if there are any Excel 4.0 macro sheets. I have known users (Including myself) to inadvertently insert an Excel 4 macro sheet with Ctrl and F11 when they intended Alt and F11 for the VBA macro editor.

However, I tested just inserting an Excel 4 macro sheet and it did not cause a problem but I am wondering if you have actually got a macro 4 sheet containing some code.

https://excel.tips.net/T002538_Detecting_Types_of_Sheets_in_VBA.html 

You could also try some Feedback by selecting File -> Feedback -> Send a frown and fill in the form.

If anyone else has some alternative suggestions then feel free to jump in and assist the OP.