Find which rectangle is clicked

[Krishna Khemraj]

I have a sheet with 100 columns. I have placed a rectangle in the top cell of each column. I would like to create a single macro that is triggered when any rectangle is clicked and the data table will sort ascending or descending based on the column the clicked rectangle resides. I am having trouble passing the identity of the clicked rectangle to the code. My problem line is the temp value of Set pShape = .ClickedRectangle. I put that there as a place holder till I figure it out.

Sub SortCurrentRectangle()
Dim pShape As Shape ' Rectangle that is clicked.
Dim pRange As Range ' Range to be sorted starting at Rectangle location and down 103 columns.
With Worksheets("Metrics")
  storeActiveCell = ActiveCell.address  'Store last selected cell
  Set pShape = .ClickedRectangle        'Pass the clicked rectangle into the code
    Set pRange = Range(pShape.TopLeftCell, pShape.TopLeftCell.Offset(103, 0)) 'Set the range to be sorted
    If Range("A2").Value = 0 Then       '0 Identifier for sorting Ascending
        Call ButtonSortMetricsPageA(pRange.address) 'Run ascending sort code
        Range("A2").Value = 1
      Else                              '1 Identifier for sorting Descending
        Call ButtonSortMetricsPageD(pRange.address) 'Run descending sort code
        Range("A2").Value = 0
    End If
    Range(storeActiveCell).Select        'Move the active cell back.
End With
End Sub

 

[Krishna Khemraj]

Figured it out.
I needed to pass the Worksheets("Metrics").Shapes with the Application.Caller identifying the name of the rectangle. The user can now click on the top of every cell in the table and the table will sort ascending or descending on that column with only one interaction.

Sub SortCurrentRectangle()
Dim pShape As Shape ' Rectangle that is clicked.
Dim pRange As Range ' Range to be sorted starting at Rectangle location and down 103 columns.
    With Worksheets("Metrics")
      Set pShape = .Shapes(Application.Caller)       'Pass the clicked rectangle into the code
        Set pRange = Range(pShape.TopLeftCell, pShape.TopLeftCell.Offset(103, 0)) 'Set the range to be sorted
        If Range("A2").Value = 0 Then       '0 Identifier for sorting Ascending
            Call ButtonSortMetricsPageA(pRange.address) 'Run ascending sort code
            Range("A2").Value = 1
          Else                              '1 Identifier for sorting Descending
            Call ButtonSortMetricsPageD(pRange.address) 'Run descending sort code
            Range("A2").Value = 0
        End If
    End With
End Sub