none
Using the value of a range for Autofilter where the shape is RRS feed

  • Question

  • I have a macro that I am using to filter out based on the value of a cell. I have placed a circle shape in each row and assigned the same macro to each shape so that if I click the shape it runs the macro that uses autofilter with Criteria1 as the value of the cell the shape is in. I have attached a picture to illustrate.

    Below is the autofilter I am using but for now I have a static range set. I need to know how to reference the cell the shape is in for Criteria1.

    ActiveSheet.Range("$a$9:$ax$500").AutoFilter Field:=12, Criteria1:=Range("H2")


    MEC


    • Edited by mecerrato Thursday, March 1, 2018 8:54 PM Clarification
    Thursday, March 1, 2018 8:51 PM

All replies

  • I received this code from a user in another forum but am getting an error and have tried searching for a solution to no avail.

    the error I am getting is "the item with the specified name wasn't found", the debug stops at

    CallingShapeName = ActiveSheet.Shapes(Application.Caller).Name

    anyone know how to fix this?


    MEC

    Friday, March 2, 2018 3:05 AM
  • Hello mecerrato,

    >>the error I am getting is "the item with the specified name wasn't found", the debug stops at

    CallingShapeName = ActiveSheet.Shapes(Application.Caller).Name

    What's the whole code in the macro?

    Could below code work for you? If not, which line occurs error?

    Sub Test()
    Dim shp As Shape
    Set shp = ActiveSheet.Shapes(Application.Caller)
    MsgBox shp.TopLeftCell.Value
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 2, 2018 3:23 AM