VBA Macro works in Excel 10 but not Excel 13

[jaykilleen]

Hi all

I have an Excel VBA Macro that is called by clicking a button called Minimise. This macro reduces the row height and, hides slicers and rows that are no longer needed so I have more room on the screen.

This works find in Excel 10, works in when I step through the code in Excel 13 but not when I run the code by clicking the button (which is just a rectangle shape with the macro assigned) in Excel 13. Its weird and I don't understand it.

Does anyone know anything that may be causing this. I have copied my code below but I can't seem to isolate the issue. My error capture says "Error 1004: The item with the specified name wasn't found". Also when my code bugs out I am on another worksheet called "Price Analysis". The other macros that are called have no issues at all when running from other code.

Any insight would be greatly appreciated

Sub CustomerMatrixRowHeight()
 
    Application.ScreenUpdating = False
 
    On Error GoTo errHandler
   
    Call Security.SecurityOff
   
    If toggle = False Then
 
        Rows("18:18").RowHeight = 15
       
        ActiveSheet.Shapes.Range(Array("Account Manager")).Visible = msoFalse
        ActiveSheet.Shapes.Range(Array("Line Of Business")).Visible = msoFalse
        ActiveSheet.Shapes.Range(Array("Market Segment Name")).Visible = msoFalse
       
        Rows("2:16").EntireRow.Hidden = True
       
        ActiveSheet.Shapes.Range(Array("Rectangle 3")).TextFrame2.TextRange.Characters.Text = "Maximise"
       
        toggle = True
   
    Else
   
        Rows("18:18").RowHeight = 120
           
        ActiveSheet.Shapes.Range(Array("Market Segment Name")).Visible = msoTrue
        ActiveSheet.Shapes.Range(Array("Line Of Business")).Visible = msoTrue
        ActiveSheet.Shapes.Range(Array("Account Manager")).Visible = msoTrue
       
        Rows("2:16").EntireRow.Hidden = False
   
        ActiveSheet.Shapes.Range(Array("Rectangle 3")).TextFrame2.TextRange.Characters.Text = "Minimise"
       
        toggle = False
   
    End If
   
    Call Security.SecurityOn
   
    Application.ScreenUpdating = True
   
    Exit Sub
   
errHandler:
 
    Call Debugging.ErrorHandler
   
    Exit Sub
   
End Sub

 

[jaykilleen]

I have followed the instructions found here:

http://www.fmsinc.com/free/NewTips/VBA/errorhandling/linenumber.html

Added line numbers manually to my code and changed my Error Handler to:

Sub ErrorHandler()
 
    MsgBox "Error Line: " & Erl & vbCrLf & _
        "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
 
    Call Security.SecurityOnOpen
 
End Sub

This has identified line 7 as the line with the error. This is the following line:

ActiveSheet.Shapes.Range(Array("Account Manager")).Visible = msoFalse

I assume this will also occur with the others lines referring to slicer visibility.

Did something to do with the way we reference slicers in Excel 13 change?

The record macro seems to handle it the same way. Now even more confused haha.

 

[Marc L]

Hi,​

​

it's amazing to use this code style for only one shape …​

   ActiveSheet.Shapes("Account Manager").Visible = False

The worksheet should be unprotected.​

​

Otherwise, first thing to do : verify if the shape exists or not.​

​

Code to insert in the worksheet module class :​

Private Sub InfoShapes()
    Debug.Print vbLf; Tab(37); "Type", " Left", " Top", " Width", " Height"
 
    For Each Sh In Me.Shapes
        Debug.Print Format(Sh.ZOrderPosition, "@@  "); Sh.Name; Tab(37); _
                    Sh.Type, Sh.Left, Sh.Top, Sh.Width, Sh.Height
    Next
End Sub

You'll see the shapes list in the VBE Immediate Watch window …​