Center a graphic in an excel cell

ChrisCana

New Member
Joined
Mar 21, 2006
Messages
16
I am working on a product comparison matrix with features in a column and the column next to it will have check marks - I am using imported .jpgs for the check marks, but I can not get them to center in a cell. They don't align like text.

Is there any way, code or otherwise, to get a graphic to center in a cell?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
If you're not tied to using a .jpg, then you can do the following:
Format the cell's font as Webdings and then enter a lower case "a" (without the quotes). This will return a check mark.

If you do need to use a .jpg so that you can assign a macro to it, then just click on the object after it is inserted and click-drag the handles to resize it and center it in the cell.

P.S. Right-click on the object, choose Format Picture>Properties and tick the Move and size with cells option.
 

ChrisCana

New Member
Joined
Mar 21, 2006
Messages
16
I would rather use the .jpg, and I cant drag the handles to resize it to the cell because each cell is a different size. So, I need to keep the .jpg at it imported size, and then center that jpg to the cell it is in. I will need to do this across multiple cells.
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
I'm not sure if it's possible to automatically center an object within a cell in the same manner that you can center text or values horizontally and vertically. Perhaps someone else can help.
 

Eric Snyder

New Member
Joined
Jun 29, 2007
Messages
4
Recently, I too was searching in vain for a method whereby a given object could be centered within a given cell. After happening upon this thread and then reading the last post, “I'm not sure if it's possible to automatically center an object within a cell…”, I must confess I grew ever-more dubious that a solution would be found. But the post continued, “…Perhaps someone else can help?” Well, I’m now happy to report that I AM that someone and it IS indeed possible to center an object within a cell. And although I frequent this invaluable board routinely, I have not contributed until today (at the urging of a colleague). The solution is as follows:
Code:
Sub CenterObjectInActiveCell()
    Dim X As Integer
    Dim Y As Integer
    X = ActiveCell.Left + (ActiveCell.Width / 2) - (ActiveSheet.Shapes(1).Width / 2)
    Y = ActiveCell.Top + (ActiveCell.Height / 2) - (ActiveSheet.Shapes(1).Height / 2)
    With ActiveSheet.Shapes(1)
        .Left = X
        .Top = Y
    End With
End Sub
Required: Paste the above procedure into a module for the active workbook, create at least one object within the active worksheet, and assign to it the above procedure.

Notes: The above procedure centers the object within the active cell, but it could easily be adapted to work on a particular cell.
 

HipGecko

New Member
Joined
Oct 28, 2015
Messages
5
i know it's been a few years, but because i can't easily find a better answer, i created this one that works --- if the image top left is anywhere in the active cell, then it'll get moved to the middle of that active cell:

Code:
Const inDebug As Boolean = False

Sub CenterPictureIfInActiveCell()
    
'If the Top-Left corner of any Picture is located within the Active Cell
'Then center the picture within the Active Cell


    Dim Pic As Picture
    
    For Each Pic In ActiveSheet.Pictures
    
        If inDebug Then MsgBox Pic.Name
    
        If isInBetween(ActiveCell.Left - 1, ActiveCell.Left + ActiveCell.Width, Pic.Left) And _
           isInBetween(ActiveCell.Top - 1, ActiveCell.Top + ActiveCell.Height, Pic.Top) _
           Then
                Pic.Left = ActiveCell.Left + ((ActiveCell.Width - Pic.Width) / 2)
                Pic.Top = ActiveCell.Top + ((ActiveCell.Height - Pic.Height) / 2)
        End If
        
    Next Pic
       
End Sub


Function isInBetween(lowVal As Long, hiVal As Long, targetVal As Long, Optional Inclusive As Boolean = True) As Boolean


'Return TRUE if the targetVal is between the lowVal and hiVal (Inclusive optional)


    isInBetween = False
    
    If Inclusive Then
    
        Select Case targetVal
            Case Is < lowVal
            Case Is > hiVal
            Case Else
                isInBetween = True
        End Select
        
        If inDebug Then MsgBox "Testing if " & lowVal & " <= " & targetVal & " <= " & hiVal & vbCrLf & vbCrLf & "Result = " & isInBetween
        
    Else
        
        Select Case targetVal
            Case Is <= lowVal
            Case Is >= hiVal
            Case Else
                isInBetween = True
        End Select
    
        If inDebug Then MsgBox "Testing if " & lowVal & " < " & targetVal & " < " & hiVal & vbCrLf & vbCrLf & "Result = " & isInBetween
        
    End If


End Function
 

Cdim7

New Member
Joined
Jan 28, 2020
Messages
1
i know it's been a few years, but because i can't easily find a better answer, i created this one that works --- if the image top left is anywhere in the active cell, then it'll get moved to the middle of that active cell:

Code:
Const inDebug As Boolean = False

Sub CenterPictureIfInActiveCell()
   
'If the Top-Left corner of any Picture is located within the Active Cell
'Then center the picture within the Active Cell


    Dim Pic As Picture
   
    For Each Pic In ActiveSheet.Pictures
   
        If inDebug Then MsgBox Pic.Name
   
        If isInBetween(ActiveCell.Left - 1, ActiveCell.Left + ActiveCell.Width, Pic.Left) And _
           isInBetween(ActiveCell.Top - 1, ActiveCell.Top + ActiveCell.Height, Pic.Top) _
           Then
                Pic.Left = ActiveCell.Left + ((ActiveCell.Width - Pic.Width) / 2)
                Pic.Top = ActiveCell.Top + ((ActiveCell.Height - Pic.Height) / 2)
        End If
       
    Next Pic
      
End Sub


Function isInBetween(lowVal As Long, hiVal As Long, targetVal As Long, Optional Inclusive As Boolean = True) As Boolean


'Return TRUE if the targetVal is between the lowVal and hiVal (Inclusive optional)


    isInBetween = False
   
    If Inclusive Then
   
        Select Case targetVal
            Case Is < lowVal
            Case Is > hiVal
            Case Else
                isInBetween = True
        End Select
       
        If inDebug Then MsgBox "Testing if " & lowVal & " <= " & targetVal & " <= " & hiVal & vbCrLf & vbCrLf & "Result = " & isInBetween
       
    Else
       
        Select Case targetVal
            Case Is <= lowVal
            Case Is >= hiVal
            Case Else
                isInBetween = True
        End Select
   
        If inDebug Then MsgBox "Testing if " & lowVal & " < " & targetVal & " < " & hiVal & vbCrLf & vbCrLf & "Result = " & isInBetween
       
    End If


End Function
This worked great! Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,096,389
Messages
5,450,112
Members
405,587
Latest member
djay52

This Week's Hot Topics

Top