Results 1 to 7 of 7

Thread: Problem with OnAction in macro running on a Mac
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2004
    Location
    Edinburgh, Scotland
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Problem with OnAction in macro running on a Mac

    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:

    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.)
    Last edited by Bill Hamilton; Jun 20th, 2019 at 06:46 AM.
    Bill H

  2. #2
    Board Regular
    Join Date
    Mar 2004
    Location
    Edinburgh, Scotland
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with OnAction in macro running on a Mac

    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?
    Bill H

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Problem with OnAction in macro running on a Mac

    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.

  4. #4
    Board Regular
    Join Date
    Mar 2004
    Location
    Edinburgh, Scotland
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with OnAction in macro running on a Mac

    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 H

  5. #5
    Board Regular
    Join Date
    Mar 2004
    Location
    Edinburgh, Scotland
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with OnAction in macro running on a Mac

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

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Problem with OnAction in macro running on a Mac

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

  7. #7
    Board Regular
    Join Date
    Mar 2004
    Location
    Edinburgh, Scotland
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with OnAction in macro running on a Mac

    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!
    Bill H

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •