Problem with OnAction in macro running on a Mac

[Bill Hamilton]

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 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:

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
' 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

The error occurs at the point indicated with the <<<<<<<<<'s.

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.13.6 with Office for Mac 2019. This runs perfectly under Windows 10, Office 365.)

 

[Bill Hamilton]

OK, this has been outstanding for over two months with no responses from the experts around here.

Is it worth trying to report it to Microsoft as a bug or can anyone come up with at least a workaround?

 

[RoryA]

I don't have my Mac here to test, but does the same thing happen if you delete that first button and then run the code again? Let me know if so, and I'll try and do some testing tonight.

 

[Bill Hamilton]

Thanks for the suggestion.

I have just tried it and it fails the same way on the (only) button on the next sheet. Both have the default name 'Button 1'. Just on the off-chance I renamed the button to something else but it made no difference.

 

[Bill Hamilton]

Is there any way of accessing a Button's properties without referring to it as a 'Shape'?

 

[RoryA]

You can refer to it as a Button specifically (and iterate the Buttons collection for the sheet).

 

[Bill Hamilton]

OK, I think I know what's going on now but have no idea how to fix it.

If I put a breakpoint into the macro code at any point after a line that opens a workbook and, when the code stops, switch views from the VBE to look at that workbook, then all its sheets are just white space. No amount of clicking, double-clicking or right-clicking anywhere will show me what's on the sheet. There's one exception to that - if I right-click where a button on the sheet shown is (when the cursor turns into a hand) then the button appears along with the data. Well, some of it. There is only one cell with text on that sheet and the rest is a big text-box (it's a Help sheet with lots of words in the box) - the TB appears along with the button but the cell with the text remains white. I tried that on different sheets and it seems only Shapes get revealed. All the Shapes on any sheet are shown when any one is right-clicked.

If I hit Debug after the macro stops when it fails to obtain the OnAction value from the Button, go to the workbook and right-click on a Shape to reveal them, then go back to the debugger and restart the code, it all works fine.

All the other buttons and shapes (I have a few Ovals with macros attached as well) on other sheets (and there are lots of them, both sheets and buttons) that are showing blank are processed correctly. It's just that pesky first button on the first sheet it's having bother with. And it doesn't matter which sheet is the first one.

Is this phenomenon (a workbook opened by code shows empty sheets while code is being executed) a standard Mac thing? Can it be overridden? And if not how can I get round it in this case?

(I did try to use the Button property but ran into the same problem as originally. I had been put off using that because when I typed in 'Dim btn as' the dropdown giving all the type options did not include the word Button, so I gave up on that as it appeared not to be a valid type. After I saw the post above, I just typed it in anyway and it wasn't objected to, but as I say, it didn't do me any good here either.)

Help!