CODEWITHSUNDEEP
excelvbaimagepositionshapes
Tam Le
Tam Le

Reputation: 378

Image/shape appears incorrectly VBA Excel

I have a very simple code to put the image to align with cell M2.

Dim picNameDefault as String
picNameDefault = "ProcessingDocument"
Dim img As Shape
Set img = ActiveSheet.Shapes(picNameDefault)
img.Top = Range("M2").Top
img.Left = Range("M2").Left
Debug.Print img.Top
Debug.Print img.Left
Debug.Print Range("M2").Top
Debug.Print Range("M2").Left

All locations (the last Debug.Print 4 lines) appears to be correct (top = top, left=left). However in the sheet, the picture move as in picture attached (first is before, second is after running code). This is a document entry UI, the picture to be moved is the document to be keyed. I have no idea what happened as other times, it works correctly. Before running code After running code

Upvotes: 0

Views: 551

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please try the next way. It is designed for clasic rotation, as you said (rotation angles: 0, 90, 270, 180):

Sub positionRotatedPict()
   Dim sh As Worksheet, img As Shape, rng As Range
   
   Set sh = ActiveSheet
   Set img = sh.Shapes("ProcessingDocument")
   Set rng = sh.Range("M4") 'According to the difference between the shape Height/Width, the cell row may be lower.
    img.top = rng.top
    img.left = rng.left
    If img.Rotation = 90 Or img.Rotation = 270 Then
       'Positions are calculated/adapted against half of the difference between the shape height and its width.
       img.top = rng.top - (img.height - img.width) / 2 
        img.left = rng.left + (img.height - img.width) / 2
    End If
End Sub

In case of (portret) images with big differences between their height and width, the positioning on top will be wrong for rows near the sheet Top.

Upvotes: 1

Related Questions