Get Name Box value of shape in current region

[PressEscape]

I wish to get the the name box value of the picture within a selected region with VBA

I can navigate and select the region. What's the trick to get the name box value (picture 27) and assign it to a variable?

 

[Jaafar Tribak]

I have got the select_shape_In_Selection code writing to debug.print but the picture number returned is a picture nowhere near the current region
(Picture 149 is returned but the picture in current region is Picture 27)
any idea's?

Have you selected the cells located beneath the picture before running the macro? and, is there more than one shape over the target selected range?

Not quite what I want. I don't want to loop through all shapes in the worksheet.

Any particular reason you don't want to loop through all shapes?

 

[PressEscape]

No and No

I want to assign a variable to the picture so I can paste it in another worksheet in a single cell

 

[Jaafar Tribak]

No and No

I want to assign a variable to the picture so I can paste it in another worksheet in a single cell

You are confusing me.

You said in an ealier post this:

Range(ActiveCell, ActiveCell.Offset(10, 0)).Select

Now, you are saying that you are not selecting the cells located beneath the picture before running the macro posted by Jolivanes !!!

 

[PressEscape]

To clarify,
I am selecting the range of cells beneath the image but when I run Jolivanes macro it returns a picture that is NOT in the range of cells selected.

 

[jolivanes]

You are selecting a cell, resizing the range and then wanting to copy and paste a shape within that range without looping through shapes on that sheet.
Just selecting the shape sounds a lot easier to me and AFAIK, you won't get it done without looping.

Re: "returns a picture that is NOT in the range of cells selected"
Not in my sheet here.
I hope that Jaafar has a solution for you because I don't with your requested restrictions anyway.

You have not answered Jaafar's question about not wanting to loop.

 

[PressEscape]

Thanks Jolivanes,
The shape (picture) is associated with part data. Part number, drawing No, Description e.t.c
I wish to select the picture relevant to the part data.
I intend to run a VBA macro that finds the part no. then copies all the data to another sheet that is laid out in a format that can use pivot tables
Looping through pictures can't achieve this.
I can get all the other cell data
All I need is to get the name box value of the picture assigned to a variable and then I can paste it, resize it and position it in a single cell.

Hope someone can help

 

[jolivanes]

If pictures or shapes are associated with part numbers, these pictures should be named accordingly.
But if you say that looping can't achieve it, then we are at the end of possibilities for me because I don't know of any other way.

 

[Jaafar Tribak]

Please, show us a preview of the shapes(s) and corresponding part data like you are showing in post#16 but including the cells addresses ie:= (rows + columns headers).
This is so we can determine exactly the offset at which the shape is located .

 

[PressEscape]

If pictures or shapes are associated with part numbers, these pictures should be named accordingly.
But if you say that looping can't achieve it, then we are at the end of possibilities for me because I don't know of any other way.

Thanks Jolivanes,
The pictures have been cut and pasted from CAD straight into the spreadsheet so the picture naming is sequential in the order pasted.
this is the point of the exercise, the current layout is a mess that I'm attempting to sort out.

 

[PressEscape]

Please, show us a preview of the shapes(s) and corresponding part data like you are showing in post#16 but including the cells addresses ie:= (rows + columns headers).
This is so we can determine exactly the offset at which the shape is located .

This is the current layout
it extends down to row 1721
That's how much data I wish to convert.
What does msoPicture achieve?