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.
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.
Immediately, the Microsoft Visual Basic for Applications window appears before us.
- Here, go to the Insert tab.
- From the options, select Module.
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.
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.
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.
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.
Similarly, attach the other images as well.
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.
- 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.
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.
Eventually, the logo gets resized according to cell C5.
Equivalently, do the same for the remaining cells.
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.
