Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Center a graphic in an excel cell

  1. #1
    New Member
    Join Date
    Mar 2006
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Center a graphic in an excel cell

    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?

  2. #2
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


  3. #3
    New Member
    Join Date
    Mar 2006
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


  5. #5
    New Member
    Join Date
    Jun 2007
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    New Member
    Join Date
    Oct 2015
    Location
    Prague
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Center a graphic in an excel cell

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •