• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 65
  • Last Modified:

How to rearrange a large group of pictures in an excel file via VBA?

I have a spreadsheet with a list of over 100 names in column A.  There is a picture next to each name.  I need to rearrange the names and pictures horizontally.  The names is easy enough, but is there a way to transpose the pictures? I did find some code that I posted below that can move the pictures, but I’m not sure if it can be modified to meet my needs.

Sub AlignMultipleShapes()
'PURPOSE: Align each shape in user's selection (first shape selected stays put)
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim Shp1 As Shape
Dim Shp2 As Shape
Dim x As Integer
Dim y As Integer

'Count How Many Shapes Are Selected
  x = Windows(1).Selection.ShapeRange.Count

'Loop Through each selected Shape (align with first selected)
  For y = 1 To x
    If Shp1 Is Nothing Then
      Set Shp1 = Windows(1).Selection.ShapeRange(y)
    Else
      Set Shp2 = Windows(1).Selection.ShapeRange(y)
        
        'Align Left
          Shp2.Left = Shp1.Left
              
        'Align Right
          Shp2.Left = Shp1.Left + (Shp1.Width - Shp2.Width)
  
        'Align Top
          Shp2.Top = Shp1.Top
  
        'Align Bottom
          Shp2.Top = Shp1.Top + (Shp1.Height - Shp2.Height)
  
        'Align Middle (Horizontal Center)
          Shp2.Top = Shp1.Top + ((Shp1.Height - Shp2.Height) / 2)
  
        'Align Center (Vertical Center)
          Shp2.Left = Shp1.Left + ((Shp1.Width - Shp2.Width) / 2)
          
      End If
  Next y

End Sub

Open in new window

0
kbay808
Asked:
kbay808
1 Solution
 
Martin LissOlder than dirtCommented:
If you move the new first shape to the .shapes(<current top shape name>).topleftcell.address the rest I assume will follow along.
0
 
Mike in ITIT System AdministratorCommented:
Could you attach a sample file with maybe 10 pictures and names for us to test with?
0
 
kbay808Author Commented:
Here is an example file
Example-File.xlsx
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
cErasmusCommented:
Hi
Do you still need help with this?
If yes do you want the names in one row with the pictures below or do you want it to be in one row with the name and the picture next to it?
Elmo
1
 
kbay808Author Commented:
I'm looking to have the pictures in one row and the names listed below them.
0
 
cErasmusCommented:
Ok i'll have something for you later today
0
 
cErasmusCommented:
Hi

See if this works for you.

Elmo
PictureMover.xlsm
0
 
cErasmusCommented:
Hi
I did not include code to set the column widths or heights to correspond with the images or names but this can be done fairly easily. let me know if you need help with this.
Elmo
0
 
kbay808Author Commented:
I do need the column widths and height to correspond with the images so that the first row is the same height as the images and the columns be wide enough for the names to fit.  

The example file works fine, but when I use the code on my file, it deletes the first image.  Not a big deal though.  It’s easy enough to copy and paste.  Thank you for your help on this.
0
 
cErasmusCommented:
Hi

I will add the code for the column height and width for you sometime tomorrow. I find it strange that it deletes it image in your workbook. If possible i would like to see the workbook.

Elmo
0
 
cErasmusCommented:
this one should work for you
PictureMover.xlsm
0
 
kbay808Author Commented:
The code works perfect in the example, but it only works on 4 pictures.  In my spreadsheet I have 291 pictures.  I added more names and pictures to the file, so you can see what is happening.
PictureMover-v2.xlsm
0
 
cErasmusCommented:
Hi

This happens when images overlap into the cell above. Unfortunately since the images do not move with the cells i do not know of a way to make sure they are contained in a cell programmatically. Make sure images do not overlap into the cells above them and it will work

Elmo
0
 
kbay808Author Commented:
The issue that I was having was that in my working file I had headers and some names did not have a picture in column B causing the code not to work.  After I remedied those 2 issues the code worked perfect.  Thank you for all your help.  Great Job!!!
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now