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

Member_8145783
Member_8145783 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
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?
Group Finance Manager
Commented:
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

Member_8145783Consultant

Author

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Roy CoxGroup Finance Manager

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

Author

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 Manager

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial