Solved Using the value of a range for Autofilter where the shape is

March 1, 2018 at 12:53:44
Specs: Windows 7
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.

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")


See More: Using the value of a range for Autofilter where the shape is

Report •

✔ Best Answer
March 1, 2018 at 19:13:08
Since I obviously don't know what process you are following, all I can do is tell you what I did and leave the rest to you.

1 - In the worksheet, I clicked Insert...Shape and chose a few shapes. I inserted a square, an arrow and a circle.
2 - Once the shapes were inserted into the sheet, I right-clicked each one and choose Assign Macro.
3 - I assigned the GetShapeInfo macro to each shape.
4 - When I click each shape, the GetShapeInfo macro runs and I get the 3 Message Boxes shown in the code.
5 - If I drag a shape to a new location, I get the same shape name, but a different address and therefore a different value.

QED

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
March 1, 2018 at 14:02:17
I've never tried it but...

Criteria1:=Range(Sheet1.Shapes("Picture 63").TopLeftCell.Address

I guess you can use any corner of a shape, e.g. BottomRightCell

https://msdn.microsoft.com/en-us/vb...

message edited by DerbyDad03


Report •

#2
March 1, 2018 at 14:32:48
That kind of worked but only for that shape as in this case "Flowchart Connector 3",
how can I set the shape name as a variable? whatever shape I click will have the
focus so maybe I can set a variable by the focus value (sorry if this doesn't make sense)

ActiveSheet.Range("$a$9:$ax$500").AutoFilter Field:=12, _
Criteria1:=Range(Sheet1.Shapes("Flowchart: Connector 3").TopLeftCell.Address)


Report •

#3
March 1, 2018 at 17:37:52
Don't take this the wrong way, but do you know how to use Google?

I have never needed "to know how to reference the cell the shape is in", but since you asked, I Googled it and got multiple hits that contained the solution that I offered. I had never even heard of using TopLeftCell before. You could have done the same thing and not have had to wait for an answer in this forum.

I have never needed to "set the shape name as a variable" but since you asked, I Googled it (specifically excel vba capture name of clicked shape) and got lots of hits related to a property known as Application.Caller. I had never even heard of the property Application.Caller before. You could have done the same thing and not have had to wait for an answer in this forum.

Less than a minute later I had written this (based on the examples I found via Google):

Sub GetShapeInfo()
 CallingShapeName = ActiveSheet.Shapes(Application.Caller).Name
   MsgBox CallingShapeName
   MsgBox Sheet1.Shapes(CallingShapeName).TopLeftCell.Address
   MsgBox Range(Sheet1.Shapes(CallingShapeName).TopLeftCell.Address)
End Sub

I don't mind helping, but I'm sure that you can use Google just as well as I can. You'd have had your answers in less time than it took you ask your questions, never mind waiting for an answer.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
March 1, 2018 at 18:19:21
Thanks for the suggestion, I honestly didn't get anything that I could use when I googled but I did not google the right keywords or the results where to difficult for me to figure out. However with the code you provided I am still 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

Thanks for the help, I am glad to hear you don't mind and certainly don't want you to think I am taking advantage :-(


Report •

#5
March 1, 2018 at 19:13:08
✔ Best Answer
Since I obviously don't know what process you are following, all I can do is tell you what I did and leave the rest to you.

1 - In the worksheet, I clicked Insert...Shape and chose a few shapes. I inserted a square, an arrow and a circle.
2 - Once the shapes were inserted into the sheet, I right-clicked each one and choose Assign Macro.
3 - I assigned the GetShapeInfo macro to each shape.
4 - When I click each shape, the GetShapeInfo macro runs and I get the 3 Message Boxes shown in the code.
5 - If I drag a shape to a new location, I get the same shape name, but a different address and therefore a different value.

QED

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#6
March 2, 2018 at 07:46:21
Dumb mistake, it works now, thanks for the help. You've helped me so much I feel like I owe you a beer :-)

Report •

Ask Question