+ Reply to Thread
Results 1 to 5 of 5

Centering images in their cells

  1. #1
    Registered User
    Join Date
    07-11-2021
    Location
    Wollongong, Australia
    MS-Off Ver
    Excel 2008
    Posts
    3

    Centering images in their cells

    So i have a file that has a bunch of steps listed with their information going across the row, shown in the attached image. In the fifth column there is space for a picture. I want to be able to centre the image within the cell. I would also like to have some form of consistent size but that is a problem for another day.

    My current macro seems to centre the images but it shifts them up and down throughout the spreadsheet into somewhat random rows. My current code is attached (can't figure out how to do it properly, am new here). to make it a bit more complicated, each row for information is actually 2 rows making selecting the next row somewhat frustrating at times.

    Hopefully I explained that well, will try to answer any questions as well as I can and any help is much appreciated.

    I have seen a blog by someone who made a macro that could tell if a picture was placed anywhere and would snap it to cells but i can't find this anymore. If you know or know of something similar that would also be great.


    Public Sub ImageCentering()

    Worksheets("Steps").Activate

    Dim wShape As Shape
    Dim PicCell As Range
    Dim Row As Integer
    Row = 2

    Do While IsEmpty(Cells(Row, 2)) = False
    If IsEmpty(Cells(Row, 2)) = False Then

    For Each wShape In ActiveSheet.Shapes
    If wShape.TopLeftCell = Cells(Row, 5) Then
    CellImageCheck = 1
    If CellImageCheck = 1 Then
    With wShape
    .Top = Range(Cells(Row, 5).Address).Top + (Range(Cells(Row, 5).Address).Height - .Height) / 2
    .Left = Range(Cells(Row, 5).Address).Left + (Range(Cells(Row, 5).Address).Width - .Width) / 2
    End With
    CellImageCheck = 0
    End If

    End If

    Row = Row + 2

    Next wShape



    End If
    Loop

    End Sub
    Attached Images Attached Images
    Last edited by Berto6; 07-11-2021 at 08:07 PM. Reason: adding images

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,678

    Re: Centering images in their cells

    Re: "each row for information is actually 2 rows"
    That does not show in your picture.
    If you're talking about "Merge & Center", stay away from that. Accident waiting to happen.

    A very longwinded macro that should explain how it can be done.
    Once when you have the hang of it, it can be shortened quite a bit.
    Please Login or Register  to view this content.
    It look likes the pictures are on the sheet already.
    Where do the originate from? A folder?

    Explain in detail what needs to happen.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,678

    Re: Centering images in their cells

    This imports a picture and places in in Cell A10 and also sizes it to Cell A10 dimensions.
    Change references (Path, Picture Name, Cell Address) as required.
    Please Login or Register  to view this content.
    If it looks like I am trying to confuse you, not so.
    It is getting late in the evening here so it'll give you a day to figure out how to proceed.

  4. #4
    Registered User
    Join Date
    07-11-2021
    Location
    Wollongong, Australia
    MS-Off Ver
    Excel 2008
    Posts
    3

    Re: Centering images in their cells

    In the picture, the first row of information uses both rows 2 and 3 because the last 2 cols have the "initial here" section if that makes sense.

    Im not trying to use the merge and centre functions. The cells are merged before the picture aspect is a seperate thing happening well before the picture.

    The images at the moment are just copy/pasted or dragged and dropped into the speadsheet. I would like an upload button eventually but thats been giving me other forms of strife.

    I hope i answered your issues, now some extra info.
    The sub should check if the row or step has info put into it, I'm checking col 2, then if col 5 has a shape in it. if both are true, the sub should grab that image/shape and centre it within the cell. (which to be fair it centres it but it will put it in a different row. I hope that makes sense.

  5. #5
    Registered User
    Join Date
    07-11-2021
    Location
    Wollongong, Australia
    MS-Off Ver
    Excel 2008
    Posts
    3

    Re: Centering images in their cells

    accidently posted twice, ignore this

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 04-13-2016, 08:46 AM
  2. Replies: 4
    Last Post: 06-11-2015, 07:55 PM
  3. [SOLVED] Centering cells on printed page
    By stardustandsunshine in forum Excel - New Users/Basics
    Replies: 19
    Last Post: 04-01-2015, 06:21 PM
  4. Replies: 2
    Last Post: 12-08-2014, 06:04 AM
  5. Replies: 0
    Last Post: 01-01-2014, 04:36 PM
  6. Replies: 2
    Last Post: 06-18-2013, 12:15 PM
  7. Applying Hyperlink Centering to all but specified cells
    By nanomess in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-31-2013, 07:01 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1