Application.Caller problem in Excel 2007

[Moph]

Okay ... so I posted a thread a while ago about vertically flipping a shape in Excel when clicked, then returning the row number in which the shape is located. Solved that problem in Excel 2003 pretty quickly using the following code:

Please Login or Register  to view this content.

Works great in Excel '03 ... but falls flat on its face in Excel '07. Application.Caller is returning the first shape in the workbook regardless of which shape is clicked. Any ideas what's happening here or a workaround?

Cheers =)

[Bob Phillips]

Works fine for me in 2007. I assigned the macro to the shape, clicked it and it worked great.

[Moph]

Yep it works for a single shape in a new workbook. Have been doing some more testing and worked out the problem, though not a solution at this stage. The shape is actually a group - a solid black triangle on a white square (see pic below). It's used as a 'collapse' / 'expand' toggle for a data set. The data sets are automatically generated by VBA code from user input on a separate sheet, and as each data set is transferred across and graphed the grouped shape is copied and pasted from a set cell on the worksheet where it is hidden behind a white, non-copyable box. Not real elegant but works beautifully in '03.

What I have worked out now is that '03 automatically assigns a new name to the shape as it is copied, so each individual data set ends up with an individual shape name on the collapse/toggle box. Excel '07 DOESN'T assign a new shape name so in copying it 'x' number of times, I am ending up with 'x+1' shapes with the same name. Hence no matter which shape I click on, it's referencing "Picture 392", the original name of the shape in A9, and returning .Row = 9.

Am trying to work around the problem at the moment by assigning a new individual name to each shape as it is pasted into position. Am trying to select the cell in which the shape has been pasted and then using ActiveSheet.Select.ShapeRange.Name = sNameNew ... but that errors out

\1

[Moph]

Well, I've solved it ... but I hate it. I cannot believe that Microsoft (1) stopped assigning copied and pasted shapes individual names in '07 and (2) made selecting a shape in a single cell by cell location so clumsy. I cannot find any way of selecting shapes in a given range or cell so have had to go with the following:

Please Login or Register  to view this content.

Given that this is a loop in a sub-routine that may be called hundreds of times to generate a single output, I could be looking at thousands of lines of additional code execution JUST because of my two points above. I HATE MICROSOFT!!!!

*heads for cold shower*