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?

 

[jolivanes]

A variation of Jaafar's suggestion in case the CurrentRegion from TopLeftCell does not work (no direct adjacent cell with a value).

Sub Maybe_So()
Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
    If shp.Type = msoPicture Then shp.Name = shp.TopLeftCell.Offset(, -2).CurrentRegion.Cells(2, 2).Value
Next shp
End Sub

This needs the first column of the area to be all values

 

[PressEscape]

Thanks guy's
I'll now work on some error checking

 

[PressEscape]

How do we add a error check if cell 2,2 is empty?

I've tried
"if shp is nothing then"
but how do I move to the next shape?


Sub Maybe_So()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Type = msoPicture Then shp.Name = shp.TopLeftCell.Offset(, -2).CurrentRegion.Cells(2, 2).Value
Next shp
End Sub

 

[jolivanes]

You are comparing a range, cell 2, 2 and an object, shp. That will not work.

 If Len(cells(2, 2))<> 0 Then

To stay with what you have in Post #33

For Each shp In ActiveSheet.Shapes
    If shp.Type = msoPicture And Len(shp.TopLeftCell.Offset(, -2).CurrentRegion.Cells(2, 2)) <> 0 Then shp.Name = shp.TopLeftCell.Offset(, -2).CurrentRegion.Cells(2, 2).Value
Next shp

 

[PressEscape]

I've got the code working
how can I then set shp.name to something like "Nothing" for every empty cell at (2,2)

 

[jolivanes]

Re: "how can I then set shp.name to something like "Nothing" for every empty cell at (2,2)"
Please elaborate the thought behind this.