Center a graphic in an excel cell

C

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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
HipGecko said:
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
Click to expand...
This worked great! Thank you!
 
Upvote 0
HipGecko said:
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
Click to expand...
Even now, on 2022 - thank You
 
Upvote 0
HipGecko said:
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
Click to expand...
Could this be modified to do Vertical OR Horizontal OR Both. Like with a message box that allows you to check mark a box for Vertical, Horizontal.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Merge and Center one Row
Replies
3
Views
45
Kaper
Kaper
E
  • Locked
  • Question Question
Center Check boxes to cell
Replies
2
Views
554
Peter_SSs
Peter_SSs
J
  • Solved
Create Word document and center title
Replies
5
Views
475
Tetra201
Tetra201
Y
  • Solved
Issue with HorizontalAlignment
Replies
8
Views
361
Peter_SSs
Peter_SSs
S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
89
sijpie
sijpie

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top