VBA & Macro Problems

How to Insert, Replace, and Center an Image in an Excel Cell Using VBA

Chanuka Geekiyanage Oct 10, 2026 869 views

Question details

The user needs a VBA solution to dynamically select an image, delete an existing picture in a specific cell (like B1), and then insert, resize, and properly center the new image within that cell.

How to Insert, Replace, and Center an Image in a Cell Using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Automating the process of updating and positioning images inside specific spreadsheet cells without the images snapping to the wrong location.
Observed behavior: Standard VBA codes often fail to replace the old image or position the new one incorrectly (e.g., appearing near cell C5 instead of B1) due to unreliable picture naming or incorrect cell anchoring.

Before you start

Ensure the Developer tab is enabled in your Excel ribbon and that you save your workbook as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code.

Solution 1Recommended

Use Target Cell Properties for Precise Image Positioning

Write a VBA script that iterates through shapes to identify and delete the old image, prompts for a new file, and accurately calculates the center position using the target cell's Top, Left, Width, and Height properties.

Relying on generic picture names (like 'Picture 1') or standard insertion methods can cause images to appear in unintended locations. The most reliable method is to anchor and size the picture by mathematically calculating its position relative to the specific dimensions of the target cell.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications editor. Go to Insert > Module to create a new blank module for your macro.

Write a loop to delete the existing image

Define your target cell (e.g., Range("B1")). Loop through ActiveSheet.Shapes and check if the shape's TopLeftCell.Address matches the target cell. If it does, execute the Shape.Delete command.

Prompt the user to select a new image

Use the Application.GetOpenFilename method to open a file picker, allowing the user to select the new image file they wish to insert.

Insert and resize the new image

Use ActiveSheet.Pictures.Insert to add the selected file. Set the picture's ShapeRange.LockAspectRatio to msoTrue, then adjust its Width or Height so it is slightly smaller than the target cell.

Calculate the exact center position

Set the image's Left property to: TargetCell.Left + (TargetCell.Width - Image.Width) / 2. Set the Top property to: TargetCell.Top + (TargetCell.Height - Image.Height) / 2. This perfectly centers the image regardless of previous anchors.

Use Target Cell Properties for Precise Image Positioning

Locking the Image to the Cell: After positioning, consider setting the picture's Placement property to xlMoveAndSize (value of 1) so that if you resize the column or row later, the image adjusts accordingly.

Free Microsoft Office alternative

Easily Manage Spreadsheets and Images with WPS Office

Troubleshooting VBA macros for precise image placement in Microsoft Excel can be frustrating and time-consuming. WPS Office provides a free, lightweight, and highly compatible alternative that makes inserting, aligning, and locking images to cells effortless, even without complex coding.

Seamless format compatibility with Microsoft Excel (.xlsx, .xlsm, .xls) files.Intuitive, built-in interface for easily centering and anchoring images to specific cells.Lightweight architecture ensures fast loading times for large spreadsheets with multiple images.Familiar UI that guarantees a zero-learning-curve migration from Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my inserted image appear in the wrong cell when using VBA?

This usually happens because the macro inserts the image based on the active cell's location or default screen coordinates. To fix this, your VBA code must explicitly define the image's Top and Left properties to match the exact coordinates of your target cell.

How can I ensure the VBA code deletes only the picture in cell B1?

Instead of deleting by generic picture names, write a loop that iterates through all shapes on the ActiveSheet. Check the 'TopLeftCell.Address' property of each shape; if it equals '$B$1', then delete it. This prevents accidental deletion of images in other cells.

Does resizing an image in VBA break its aspect ratio?

It can if you force both Width and Height independently. To maintain the original proportions, make sure to set 'ShapeRange.LockAspectRatio = msoTrue' before you adjust the dimensions of the newly inserted picture.

What is the formula to perfectly center an image inside a cell using VBA?

After resizing the image to fit inside the cell, calculate the center by setting the image's Left property to 'Cell.Left + (Cell.Width - Image.Width) / 2' and its Top property to 'Cell.Top + (Cell.Height - Image.Height) / 2'.

Excel & Data Problems