How can I change the shape size of a shape by clicking another shape?-CodePudding

I have multiple shapes that when clicked it's doing things in my Excel sheet (my code below). But apart from these things I want to change the size of a shape that is positioned 2 cells to the right of the shape I'am clicking. How can I do this without referring to the name of the shape I want to resize?

This is my code.

    Sub ArrowClick()
        With ActiveSheet.Shapes(Application.Caller).TopLeftCell
             .EntireRow.Borders(xlEdgeBottom).LineStyle = xlNone
             With .EntireRow.Offset(1, 0).Resize(9)
                 .EntireRow.Hidden = Not .Hidden
        End With
    End With

    End Sub

CodePudding user response：

Please, use the next way. It will select the shape on the second column of the clicked shape row and double its width:

Sub ArrowClick()
    Dim nextSh As Shape
    With ActiveSheet.Shapes(Application.Caller).TopLeftCell
        .EntireRow.Borders(xlEdgeBottom).LineStyle = xlNone
        With .EntireRow.Offset(1, 0).Resize(9)
                .EntireRow.Hidden = Not .Hidden
        End With
        Set nextSh = findNextSh(.Offset(0, 2).Address)
        If Not nextSh Is Nothing Then
               nextSh.width = nextSh.width * 2
               nextSh.Select
        End If
    End With
End Sub

Function findNextSh(strRange As String) As Shape
   Dim sh As Shape
   For Each sh In ActiveSheet.Shapes
        If sh.TopLeftCell.Address = strRange Then
            Set findNextSh = sh: Exit Function
        End If
   Next sh
End Function