• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Working with Pictures in Excel

C

ChandooAdmirer

New Member
Am a beginner, but am really interested to learn more


Have created a table in excel sheet, with multiple rows(products) and columns(product description, cost, part no and image)

One of the columns contains the image of the product. For each product(row) there is one image column

When i insert the image, and modify any content in any of the rows, or alter the width of the column/height of the row, the images all get jumbled up, and shrink in size

Is there a way to insert multiple images into multiple excel cells, so that the all the excel cells are of the same size. I mean after inserting the images, the images get automatically re sized to the same size


Why do the images all get distorted. This also happens when i delete the contents of the table, the images yet remain

Please let me know


Thanking you

ChandooAdmirer
 
For almost all images in an XL sheet, there is a size/move properties that you'll want to look at. For you, right click on on of the images, select "Size and Properties", and then the Properties tab. You'll see 3 important choices.


The first causes images to move and change shape as you move cells. This, sadly, is the default choice, and can cause images to get all distorted. You probably want to pick one of the other two options.


Now, if you want to make all the shapes the same size, the easiest way would be to run a quick macro. First, to install this macro, right click on your sheet tab, view code. This will open the Visual Basic Explorer. At the top, go to Insert - Module. Paste the following into the area that opens up, then close the VBE (don't need to save)

[pre]
Code: 
Sub AutoShapes()
x = ActiveCell.Height
y = ActiveCell.Width
For Each sh In ActiveSheet.Shapes
sh.Width = y
sh.Height = x

'Choices are xlMove, xlMoveAndSize, or xlFreeFloating
sh.Placement = xlFreeFloating 'Don't move or size
Next sh

End Sub
[/pre]
Now, on the sheet with your shapes, select a cell somewhere that is the correct height and width that you want your pictures. Then, hit Alt+F8 to bring up the Macro menu, select the AutoShapes macro, and press run. Voila! All your shapes/images should now be the same size as the cell you selected, and won't change size when you move rows.
 
Dear Luke, Great tried it. It works for one application.

However yet have an issue.

Maybe i didnt explain earlier

The problem is everytime i paste pictures into excel, through insert a file, the pictures are of different sizes. I have to format each picture in every cell, and resize the picture of every cell. This is really time consuming

Is there a way i insert all the pictures into the excel cells, and then automatically resize the pictures to the size of the cell

Please let me know

Thanking You
 
I'm not sure I understand your problem, as what you describe is what the macro does. Granted, you'll need to have inserted all the pictures before running the macro (doesn't auto-resize as pictures are added), but it changes the size of all pictures on your worksheet to the size of the selected cell.


Is this not what you wanted, or is there something additional that I am not seeing?
 
Dear Luke, thank you so much for responding. You are right, it does help in doing what i want. Yes i did not specify my requirement properly

Basically i have an excel sheet, where the row heights are different for each row, I have one column where i need to place the images. What happens is when i insert the images into the required cell, i have to resize the image as per the row height and column width. This has to be repeated for every row(every image insertion)

When i enter text in a particular cell, example with wrap text or merge and center option, the row height changes automatically to accomadate the text allowing me fit it into one cell


Similarly when i insert any image, can there be a way that the image resizes itself to the cell dimensions(which is based on the row height and column width) In this way the image gets accomadated to the size of the cell. So whether i put a big picture or small picture the picture gets resized to that of the cell dimensions. In this way i can fill all my excel data row by row for each of the colums like product code, product description, product remarks, and just insert all pictures one row at a time into the column "product image

Am sorry have a lengthy email. Will prepare an excel sheet if you may require to clarify myself. Let me know

Thank you Luke
 
Hmm. In that case, I think you'll want all the shapes to change with row size (reverse of what we did before). Then, just need to initially set all the shapes. It's probably faster to just do it manually, but there is a keyboard shortcut. If you hold down the Alt key while changing the shape size, it will 'snap' to the cell corners, making it a little easier. Again, if the sizing property is changed to "size with cell" then now that you have it aligned to cell boundaries, the shape will grow/shrink as the row height increases/decreases.
 
Hi !


Each time I insert an image in a cell by a VBA code, the image is resized within the cell's dimensions without distortion.


So ChandooAdmirer, if in your case it's also by a VBA code, share it so that I can modify it …
 
Marc, its good to have another input. Thank you for sharing your thoughts. As am a beginner can you share with me the VBA code on how to insert the image. Can it be inserted as a macro, the way that Luke provided me the code in the earlier post. Let me know, as it will be very helpful
 
Luke also one more query. Is there a way i can select all the images and then apply the property size with cell. Tried using ctrl key and selecting the images, and applying the property, it didnt seem to work
 
We can use the macro I posted earlier, and just comment out the lines we don't care about.

[pre]
Code: 
Sub AutoShapes()
x = ActiveCell.Height
y = ActiveCell.Width
For Each sh In ActiveSheet.Shapes
'sh.Width = y
'sh.Height = x

'Choices are xlMove, xlMoveAndSize, or xlFreeFloating
sh.Placement = xlMoveAndSize 'Change this to one of 3 choices
Next sh

End Sub
[/pre]
 
Each time I insert an image in a cell by a VBA code, the image is resized within the cell's dimensions without distortion.



 


As am a beginner can you share with me the VBA code on how to insert the image.
Click to expand...

 

Example :

[pre]
Code: 
    W! = ActiveCell.Width - 4

With ActiveSheet.Pictures.Insert(PictAddress)
.LockAspectRatio = msoTrue
.Height = ActiveCell.Height - 4
If .Width > W Then .Width = W
.Placement = xlMoveAndSize
.IncrementLeft (ActiveCell.Width - .Width) / 2
.IncrementTop (ActiveCell.Height - .Height) / 2
End With
[/pre]
 
Dear Marc am not familiar with VB coding as yet. When i right click the sheet, click on view code, and then copy and paste the code into the active window, and close. It does not show up as a macro when i click on alt+f8

How do i execute this code. Please can you share more. Will really appreciate it
 
Dear Friends, am yet having problem with my main requirement of Auto resizing images while importing/pasting in Microsoft Excel. Basically need the image to auto resize to the size of the cell.

Marc and Luke, thanks for the inputs. Would really appreciate if you could help me with this, as am not able to make headway as yet on this requirement
 
I'm a little confused as to what your requirement is at this point. So far, I've proved 3 macros that:

Change all shapes to cell size

Change a single shape to cell size

Change all shapes size/move property


What is it that you are requiring that is different from previous?
 
Good day ChandooAdmirer


You cannot insert a picture into a cell. You can only insert a picture over a cell.

This link may help you

http://www.mrexcel.com/forum/excel-questions/52426-resize-picture-cell-size.html
 
Dear Marc, tried running the code you had provided. As i understand when i run this macro, it will allow me to insert images, one by one, and auto resize this image to the cell dimensions

please correct me if i am wrong

However i get an error when i run the macro

Sub AutoShapes()

W! = ActiveCell.Width - 4


With ActiveSheet.Pictures.Insert(PictAddress)

.LockAspectRatio = msoTrue

.Height = ActiveCell.Height - 4

If .Width > W Then .Width = W

.Placement = xlMoveAndSize

.IncrementLeft (ActiveCell.Width - .Width) / 2

.IncrementTop (ActiveCell.Height - .Height) / 2

End With


End Sub

It gives me an error with a cross mark 400

Can you please correct the code, so that i can execute it and avoid any errors
 
Hi,


my code works with two different Excel versions.


In which line the error ?


Maybe you forgot to adapt the code to your needs, Copy & Paste is not programming !

So you must replace PictAddress by the image's location file …

Or using a variable like in the example of bobhc's link …


And to learn, you shoud use the Macro Recorder to see its generate code, a good beginner basis …
 
You must log in or register to reply here.
Back
Top