Insert Pictures Automatically Size to Fit Cells with VBA in Excel

Get FREE Advanced Excel Exercises with Solutions!

Thankfully, a lot of us utilize Excel in our professional environments. Excel is used in every commercial organization to create databases and organize data as needed. Another intriguing feature is the ability to effortlessly input pictures that are automatically resized to fit cells. In this article, we’ll demonstrate 2 simple and time-saving techniques to insert pictures that will automatically size to fit cells using the VBA macros in Excel. So, to understand more and conserve time, read the entire article properly.


How to Insert Pictures Automatically Size to Fit Cells Using VBA in Excel: with 3 Steps

For ease of understanding, we are going to use a dataset of Logo of Different Companies. This dataset includes the Company names and their corresponding Logos in columns B and C respectively.

insert pictures in excel automatically size to fit cells vba

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Now, we’ll use this dataset to insert pictures automatically sized to fit cells using VBA in Excel.
Not to mention, we have used the Microsoft Excel 365 version here, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.


Step 01: Insert Module

In this method, we’ll show how you can insert picture into Excel cells and resize them according to the cell size with just one VBA macro. So, without further delay, let’s dive in.

  • At the very beginning, navigate to the Developer tab.
  • Then, click on Visual Basic on the Code group of commands.

Insert Module

Immediately, the Microsoft Visual Basic for Applications window appears before us.

  • Here, go to the Insert tab.
  • From the options, select Module.

Inserting Module to insert picture automatically size to cells

Instantly, it will insert a code module on the right side of the display.


Step 02: Write VBA Code

In this step, we’ll build the working script to fit the pictures to the cell size.

  • Therefore, paste the following code into Module1 which we have just created.
Sub Insert_Automatically()
Dim Ph_Path As Variant
Dim Ph As Picture
Ph_Path = Application.GetOpenFilename(Title:="Select Your Desired Photo")
If Ph_Path = False Then Exit Sub
Set Ph = ActiveSheet.Pictures.Insert(Ph_Path)
With Ph
.Width = ActiveCell.Width
.Height = ActiveCell.Height
.Placement = 1
End With
End Sub

💥 Code Breakdown

  • Firstly, we named the sub-procedure as Insert_Automatically.
  • Secondly, we declared the two variables.
  • Thirdly, we used the GetOpenFilename method to pick our desired file.
  • Then, we set the width and height of the picture the same as the size of the selected cell.

Write VBA Code to insert pictures automatically size to fit cells in Excel


Step 03: Run the Macro

  • After that, select cell C5 where we want to place the logo of Softeko.
  • Then, proceed to the Developer tab.
  • Following this, click on Macros in the Code group. Alternatively, you can press ALT+F8 on your keyboard to replicate the task.

Run the Macro

Suddenly, the Macro dialog box will pop up.

  • Consequently, select the Insert_Automatically macro from the Macro name box and click on the Run button.

macro dialog box to run the vba code in excel

Just after clicking on this, the Select Your Desired Photo window will open up.

  • Hereafter, select the preferred image and tap on the Open button.

As a result, you can see the logo in cell C5 at the left-top corner of this cell.

inserting pictures automatically fit to cells size in excel

Similarly, attach the other images as well.

Procedure to Insert Pictures Automatically Size to Fit Cells Using VBA in Excel


How to Insert Pictures Manually to Fit Cells in Excel

In this method, we’ll insert the pictures manually. Then, using a VBA macro, we’ll resize them to fit cells in Excel. So, without further ado, follow these simple steps below.

📌 Steps:

  • First, select the cell where we have to input the logo. In this case, we have selected cell C5.
  • Secondly, advance to the Insert tab.
  • Thirdly, click on the Illustrations drop-down icon.
  • Again, tap on Pictures.
  • Then, select This Device as the source of the Insert Picture From option.

How to Insert Pictures Manually to Fit Cells in Excel

  • In the Insert Picture dialog box, select the desired image and click Insert.

Correspondingly, the picture gets attached to the worksheet, but it comes with the source size, which is large enough to fit itself into the cell of this worksheet.

source pictures

To resize it, follow the next steps.

  • Initially, open the Visual Basic Editor like before and insert another code module.
  • Then, paste the following code into Module2.
Public Sub Size_Fit_Cell()
On Error GoTo Select_Image
Dim ZImageWtoHRatio As Single
Dim QWtoHRatio As Single
With Selection
ZImageWtoHRatio = .Width / .Height
End With
With Selection.TopLeftCell
QWtoHRatio = .Width / .RowHeight
End With
Select Case ZImageWtoHRatio / QWtoHRatio
Case Is > 1
With Selection
.Width = .TopLeftCell.Width
.Height = .Width / ZImageWtoHRatio
End With
Case Else
With Selection
.Height = .TopLeftCell.RowHeight
.Width = .Height * ZImageWtoHRatio
End With
End Select
With Selection
.Top = .TopLeftCell.Top
.Left = .TopLeftCell.Left
End With
Exit Sub
Select_Image:
MsgBox "Choose an Image and Run the Macro."
End Sub

  • After that, save the Excel file.

At this moment, we have to run this code.

  • Firstly, click on the picture.
  • Then, jump to the Developer tab.
  • Henceforth, click on the Macros icon.

Instantly, the Macro wizard appears before us.

  • Afterward, select Size_Fit_Cell macro and click on the Run button.

Insert Pictures Manually to Fit Cells size in Excel

Eventually, the logo gets resized according to cell C5.

Equivalently, do the same for the remaining cells.

How to Insert Pictures Manually to Fit Cells size in Excel


Practice Section

For doing practice by yourself, we have provided a Practice section like the one below on each sheet on the right side. Please do it yourself.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

This article explains how to insert pictures automatically size to fit cells using VBA in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo