How to select picture at activecell by VBA?

[London12F]

The activecell is now at say B56 by another VBA.

How to select existing picture with topleft at that cell (B56) by VBA?

<colgroup><col></colgroup><tbody>
</tbody>

 

[JoeMo]

Sub SelectPic()
Dim Pic As Picture
For Each Pic In ActiveSheet.Pictures
       If Pic.Left = Range("B56").Left And Pic.Top = Range("B56").Top Then
              Pic.Select
              MsgBox Pic.Name
       End If
Next Pic
End Sub

 

[London12F]

How to apply to activecell, not only to B56 only?

 

[JoeMo]

How to apply to activecell, not only to B56 only?

Replace Range("B56") with Activecell ...... Activecell.Top .....

 

[London12F]

JoeMo,

It cannot work when I revise VBA as follows:

Sub dSelectPic()
Dim Pic As Picture
For Each Pic In ActiveSheet.Pictures
If Pic.Left = ActiveCell.Left And Pic.Top = ActiveCell.Top Then
Pic.Select
MsgBox Pic.Name
End If
Next Pic
End Sub


My expected result:
1. The activecell move to say B16 manually.
2. The picture at that cell (B16) is selected by VBA.

NB. In fact the previous VBA also can't work at B16 only.

 

[JoeMo]

JoeMo,

It cannot work when I revise VBA as follows:

Sub dSelectPic()
Dim Pic As Picture
For Each Pic In ActiveSheet.Pictures
If Pic.Left = ActiveCell.Left And Pic.Top = ActiveCell.Top Then
Pic.Select
MsgBox Pic.Name
End If
Next Pic
End Sub


My expected result:
1. The activecell move to say B16 manually.
2. The picture at that cell (B16) is selected by VBA.

NB. In fact the previous VBA also can't work at B16 only.

Maybe your expected picture is not aligned to the activecell. Try this code - changing the name of the picture (in red) to whatever picture you want to align. What do you get?

Sub SelectPic()
Dim Pic As Picture
For Each Pic In ActiveSheet.Pictures
       If Pic.Name = "Picture 1" Then
              Pic.Left = ActiveCell.Left
              Pic.Top = ActiveCell.Top
       End If
       If Pic.Left = ActiveCell.Left And Pic.Top = ActiveCell.Top Then
              Pic.Select
              MsgBox Pic.Name
       End If
Next Pic
End Sub

 

[London12F]

It's only work if activecell is at Picture 1.

If activecell is at another picture, Picture 1 will move to that activecell.

(i.e. Picture 1 overlap with that Picture at activecell.)

How to revise so that the VBA can apply to all pictures, instead of to Picture 1 only?

<colgroup><col></colgroup><tbody>
</tbody>

 

[JoeMo]

It's only work if activecell is at Picture 1.

If activecell is at another picture, Picture 1 will move to that activecell.

(i.e. Picture 1 overlap with that Picture at activecell.)

How to revise so that the VBA can apply to all pictures, instead of to Picture 1 only?

<tbody>
</tbody>

Sorry, I don't understand what you want. You want ALL pictures on the active sheet to align with the active cell??

 

[London12F]

My actual case:

There are pictures at Q1, R3, U2 etc.

By my VBA, the cell may be moved to anywhere.

The expected result of your VBA are:

If the cell is moved to say Q1 by my VBA,

your VBA will select the picture at that cell (Q1).

<colgroup><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Does this macro do what you want...

Sub SelectPictureAtActiveCell()
  Dim Pic As Shape
  For Each Pic In ActiveSheet.Shapes
    If Pic.TopLeftCell.Address = ActiveCell.Address Then
      If Pic.Type = msoPicture Then
        Pic.Select
        Exit For
      End If
    End If
  Next
End Sub