Center an image in a cell (top to bottom and left to right)

[jserow]

I am trying to center an image in a cell. the cell is quite large as is the image. Can anyone assist me with this?

Thanks,
John Serow

 

[Rick Rothstein]

I am trying to center an image in a cell. the cell is quite large as is the image. Can anyone assist me with this?

You gave us no specifics, so I used made up data that you will have to adjust to match your actual setup...

Sub CenterPicture()
  Dim Cell As Range, Image As Shape
  Set Cell = Range("G9")
  With Sheets("Sheet1").Shapes("Picture 1")
    .Top = Cell.Top + (Cell.Height - .Height) / 2
    .Left = Cell.Left + (Cell.Width - .Width) / 2
  End With
End Sub

 

[jserow]

Sorry about the non-specified data. I am using a spread sheet for a Certificate of Analysis (CoA) and have a large open cell at the top that I put the company header on, the header is a jpg image and it is really difficult to get it centered in the cell (left to right and top to bottom). I am using Excel 2007.

Rick,
When I tried your code I got an error message stating "Subscript out of range."

I appreciate the assistance with this issue.
John Serow

 

[Rick Rothstein]

Rick,
When I tried your code I got an error message stating "Subscript out of range."

I did say "you gave us no specifics" which meant I had to guess at a couple of things... my expectation was that you would change those guesses to meet your actual setup (if you are not going to give specific information about your setup and needs, you will have to learn how to spot the things that will need changing). Here is the code I posted, modified slightly (I moved one line of code)...

Sub CenterPicture()
  Dim Cell As Range, Image As Shape
  With Sheets("[COLOR="#008000"][B]Sheet1[/B][/COLOR]").Shapes("[B][COLOR="#FF0000"]Picture 1[/COLOR][/B]")
    Set Cell = .Range("[COLOR="#0000FF"][B]G9[/B][/COLOR]")
    .Top = Cell.Top + (Cell.Height - .Height) / 2
    .Left = Cell.Left + (Cell.Width - .Width) / 2
  End With
End Sub

The blue text is the cell address that you want to center the picture on (assumed to be on the active sheet when you run the code)... the green text is the sheet name that contains the cell and picture... the red text is the name of the picture control that your picture was loaded into.

 

[sdspieg]

Great macro - thanks. Worked well for me. But is there also a way to make this 'sticky' - in such a way that if we copy that cell with the centered image, the image will remain centered? When I try this now, the centering disappears again. To explain the use case - I have some data that I want to give a green arrow up, a red arrow down, a yellow arrow to the right (for data that stay even) and a waiting glass (for data that aren't available yet). I was thinking to at least do these 'manually' with your macro and then top copy/paste, but if there's a smarter way of doing this (in an automated way), I'd obviously be interested! Thanks much...

UPD - I forgot to add that I'm trying to find more 'visible' ways of showing this than the built-in arrow symbols... There are better images out there that are a lot more visible.