Hi Tony,
So columns A-C , Rows 1-22 in your sheet looks something like this?
- And if you single left click on blue oval shape, you want it to short Ascending the values in range B8:B1003?
- And if you single left click on the red rectangular shape, you want it to short Ascending the values in range C8:C1003?
If so, yes, it can be done.
But you will have to assign all shapes to the following subroutine/macro (copy the sub name
Sort_Column_Below_Shape_That_Is_Clicked_On) and then right click on each shape, select assign macro, paste the sub name into the search bar, and press enter.
But before doing this, please see the statement below the code block below!
VBA Code:
Sub Sort_Column_Below_Shape_That_Is_Clicked_On()
Dim nameOfSheetTabIAmIn As String
nameOfSheetTabIAmIn = ActiveSheet.name
Dim firstRowToSort As Long
firstRowToSort = 8 'Hard coded in.
Dim columnLetter As String
Select Case Sheets(nameOfSheetTabIAmIn).Shapes(Application.Caller).name
Case "Oval 1"
columnLetter = "B"
Case "Rounded Rectangle 2"
columnLetter = "C"
'Case "(Name of shape in column D)"
' columnLetter = "D"
'.
'.
'.
'.
'Case "(Name of shape in column Q)"
' columnLetter = "Q"
'Case Else
End Select
Dim lastRow As Long
lastRow = Sheets(nameOfSheetTabIAmIn).Range(columnLetter & firstRowToSort).End(xlDown).row
lastRow = 1003 'Delete (or comment out) this row if you want it to automatically find the last non-blank cell in this column!!
Dim rangeToSortAddress As String
rangeToSortAddress = columnLetter & firstRowToSort & ":" & columnLetter & lastRow
Sheets(nameOfSheetTabIAmIn).Range( _
Sheets(nameOfSheetTabIAmIn).Range(columnLetter & firstRowToSort), _
Sheets(nameOfSheetTabIAmIn).Range(columnLetter & lastRow) _
).Sort Key1:=Sheets(nameOfSheetTabIAmIn).Range(columnLetter & 1), Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers
End Sub
To get the names of each of the shapes, you can select on the shape
before you assign them to the macro with the following sub. So to get the name, left click on the shape once and then run the following line of code from the code window. Then put it in the
Select Case statement with the appropriate corresponding column letter.
VBA Code:
Sub GetNameOfShape()
MsgBox Selection.name
End Sub
But the natural question becomes . . . why don't you select range
B7:Q1003 and press Ctrl T to make it an Excel Table (with filters) so that when you click on the arrow, you can sort it from there instead? (I guess you have a reason, but just mentioning this just in case!)