Search code examples
excelvbatitleshapes

Show or hide a series of rows with the same defined name as the title of the shape, by clicking on the shape


I am trying to replicate a subroutine from another workbook. The subroutine in the original workbook works.

The syntax are identical for both subroutines.

Sub FigurMedTittel_Klikk()

    Dim Tittel As String
    
    Tittel = ActiveSheet.Shapes(Application.Caller).Title
    
    Select Case Tittel
    Case "BME"
        Range(Tittel).EntireRow.Hidden = False
        Range("BMA").EntireRow.Hidden = True
        Range("Kompleks").EntireRow.Hidden = True
    Case "BMA"
        Range(Tittel).EntireRow.Hidden = False
        Range("BME").EntireRow.Hidden = True
        Range("Kompleks").EntireRow.Hidden = True
    Case "Kompleks"
        Range(Tittel).EntireRow.Hidden = False
        Range("BMA").EntireRow.Hidden = True
    End Select

End Sub

The point of the sub is to either show or hide a series of rows with the same defined name as the title of the shape, by clicking on the shape.
The problem seems to be that I need to give the shape (a rectangle) a title.

When I try to run the code I get

"Run-time error '-2147352571 (80020005)'"


Solution

  • I'm not having much luck reading the .Title property - you could use the text in the shape or its AlternativeText (set via Excel Drawing Tools > Format > Alt Text)

    Sub FigurMedTittel_Klikk()
    
        Dim shp As Shape, txt
        
        txt = Application.Caller
        
        'or  one of these other methods...
        'Set shp = ActiveSheet.Shapes(Application.Caller)
        'txt = shp.TextFrame2.TextRange.Text 
        'txt = shp.AlternativeText
        
        For Each r In Array("BME.", "BMA.", "Kompleks.")
            Range(Replace(r, ".", "")).EntireRow.Hidden = (txt <> r)
        Next r
         
    End Sub