I raised this question on the MrExcel forum almost two years ago with no resolution forthcoming, so I think it's reasonable to post it again here without being accused of cross-posting. The MrExcel url is https://www.mrexcel.com/board/thread...light=onaction. There were a few replies but nothing came of them.
So, here goes again. I hope someone here can help.
I have a macro which runs under Windows perfectly well. I'm having to convert the system it runs in to run on a Mac, and it fails at a very frustrating point. It's to do with checking/reassigning the macros that are invoked by clicking on shapes that might be on any sheet in the workbook. These shapes include Ovals and Buttons (neither are ActiveX).
The failure on the Mac happens when assigning the existing OnAction macro name to a variable in order to check what it is. The failure occurs with the first Button on the first sheet, after having checked and ignored a couple of TextBoxes that have no macro assigned and no error is given with the OnAction assignment. The error is
Run-time error '1004': Method 'OnAction' of object 'Shape' failed.
Here's the code. (The basic functionality was obtained from https://www.thespreadsheetguru.com/b...-worksheet-vba, althogh I tweaked it a little).
The error occurs at the point indicated with the <<<<<<<<<'s. I have also discovered that the error occurs with any reference to 'sShp.OnAction', even a Debug.Print.Sub ResetMacrosTest() 'PURPOSE: Remove any external workbook references from all shapes triggering macros in given workbook 'Source: www.TheSpreadsheetGuru.com Dim stwsSht As String, stCtrl As String, stMacroLink As String, stNewLink As String Dim vSplitLink As Variant Dim wsSht As Worksheet, sShp As Shape Application.ScreenUpdating = False wbWork.Activate ' this w/b has already been opened ' Loop through each shape in each worksheet For Each wsSht In wbWork.Worksheets stwsSht = wsSht.Name For Each sShp In wsSht.Shapes stCtrl = sShp.Name ' (this is only here so I can see in the debugger which shape is being processed) stMacroLink = sShp.OnAction '<<<<<<<<<<<< On a Mac, this always fails on first Button on first sheet. ' Determine if shape was linking to a macro and if so ensure the pathname is removed from its location . If stMacroLink <> "" And InStr(stMacroLink, "!") <> 0 Then vSplitLink = Split(stMacroLink, "!") ' vSplitLink(0) is the w/b (path&)name, vSplitLink(1) is the macro name. If vSplitLink(0) <> wbWork.Name Then ' this basically removes the full pathname from the link, if present. stNewLink = wbWork.Name & "!" & vSplitLink(1) sShp.OnAction = stNewLink ' Apply New Link End If End If Next sShp Next wsSht End Sub
I haven't a clue where to go with this one, so any advice is welcome. I have encountered a few differences between the Windows and Mac VBAs during this conversion exercise, mainly in the Shapes department. Maybe this is another, but I can't find the workaround.
(Using a Macbook Pro, OS10.15.7 Catalina with Office for Mac 2019. This runs perfectly under Windows 10, Office 365.)