Toggle show/hide button not working on Excel for Mac but fine on Windows - VBA code needs adjusting

Does anyone know what I need to change to get this VBA code to work in Excel for Mac? It works fine in Windows.

It is a show/hide toggle button for rows while the caption on the button changes as well. I used a form button (not ActiveX).

Sub HideDone()
Dim Shp As Shape
   
   Set Shp = ActiveSheet.Shapes(Application.Caller)
   If Shp.TextFrame.Characters.Text = "Hide Done" Then
      Shp.TextFrame.Characters.Text = "Show All"
      Range("A1").AutoFilter 10, "<>11. Published", xlAnd, "<>Cancelled"
   Else
      Shp.TextFrame.Characters.Text = "Hide Done"
      ActiveSheet.ShowAllData
   End If
End Sub

Open in new window


The message I get is
Run-time error 1004
Method 'TextFrame' of object 'Shape' failed

This code is highlighted in yellow
If Shp.TextFrame.Characters.Text = "Hide Done" Then
Member_8145783ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
I would guess that MAC shapes don't have a TextFrame. Try adding a CommandButton and this code

Option Explicit

Private Sub CommandButton1_Click()
    With Me.CommandButton1
        If .Caption = "Hide Done" Then
            .Caption = "Show All"
            Range("A1").AutoFilter 10, "<>11. Published", xlAnd, "<>Cancelled"
        Else
            .Caption = "Hide Done"
            ActiveSheet.ShowAllData
        End If
    End With
End Sub

Open in new window


It appears that MACs don't have an Object Browser in the VB Editor so I can't explain how you check if I'm right about the TextFrame.

Is there a macro recorder in your version MAC VBA?
Roy CoxGroup Finance ManagerCommented:
Bit more searching and I'm not sure if MACs have activeX controls, so try adding a button from the forms controls and assign this macro. Maybe also test it on your original shape.

Option Explicit

Sub HideDone()
    Dim Shp As Shape

    Set Shp = ActiveSheet.Shapes(Application.Caller)
    Shp.Select

    With Selection.Characters
        If .Text = "Hide Done" Then
            .Text = "Show All"
            '      Range("A1").AutoFilter 10, "<>11. Published", xlAnd, "<>Cancelled"
        Else
            .Text = "Hide Done"
            '      ActiveSheet.ShowAllData
        End If
    End With
    Range("A1").Select
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Member_8145783ConsultantAuthor Commented:
Hi Roy

Mac does not support ActiveX.

I have someone else looking at this for me at the moment (not from EE) so if they are unable to solve I will try this.

Thanks.
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Roy CoxGroup Finance ManagerCommented:
In my last post I said that ActiveX are not supported. The code I posted works with Shapes and Forms buttons. I can't test on a MAC though, but from reading several different pages I think it might.
Member_8145783ConsultantAuthor Commented:
I was simply confirming ActiveX was not supported as your last post said – 'I'm not sure if MACs have activeX controls'.

Thanks for the code - it worked. I will close this question and mark your answer as solved it.
Roy CoxGroup Finance ManagerCommented:
That's good to know. It seems to me that MAC VBA  does not support the newer innovations in Excel VBA. I know for a while MACs had no VBA.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows OS

From novice to tech pro — start learning today.