CODEWITHSUNDEEP
excelvba
FinDev
FinDev

Reputation: 439

VBA: How to center every single image in one excel sheet in their current cells

The code below will center an image within a cell, given that you know both the name of the image and the cell that it is located in. Is anyone familiar with a dynamic loop that will search a single sheet for every image in that sheet and center every image in its current cell? If this isnt possible is there a way to change the code so that you can loop through a specific range? For example A4:A10

    Sub CenterImages()
        With ActiveSheet.Shapes("Picture 1")
            .Top = Range("B1").Top + (Range("B1").Height - .Height) / 2
            .Left = Range("B1").Left + (Range("B1").Width - .Width) / 2
        End With
    End Sub

Upvotes: 2

Views: 5543

Answers (1)

BigBen
BigBen

Reputation: 50162

Perhaps using Shape.TopLeftCell, something like this:

Sub CenterImages()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
       If shp.Type = msoPicture Then
           With shp
               .Top = .TopLeftCell.Top + (.TopLeftCell.Height - .Height) / 2
               .Left = .TopLeftCell.Left + (.TopLeftCell.Width - .Width) / 2
           End With
       End If
    Next
End Sub

If you want to restrict this to pictures falling within a certain range, perhaps change to

If shp.Type = msoPicture _
   And Not Intersect(shp.TopLeftCell, ActiveSheet.Range("A4:A10")) Is Nothing Then

Upvotes: 4

Related Questions