How to Lock Pictures in Excel Cell

Do you need to insert an image, picture, object or shape in your report or dashboard? In this blog post, you will learn how to insert an image or picture in Excel and set the picture properties to lock.

By default, the inserted image, object, or shape does not stick with the respective cell. If you adjust the height or width of the adjacent cell, the inserted objects will not move with the cell. It works as a floating object in Excel.

The locked image or picture moved with the cell when you resize the cell and hide/unhide the rows or columns.

This blog post explores Excel Format Picture options to Lock Pictures in Excel. Also, it guides you on how dynamically move or resize the inserted image using Excel VBA or Office Script code to the currently selected cell, which will be useful for your automation or dashboard projects.

Suppose you have a list of Products in Column B and the Category in Column A. You need to insert an image or icon for individual products and create an interactive report.  You need to add a category slicer for the user to select a specific category and filter the related products in the report.

Get your copy of the example workbook used in this blog post to follow along!

Insert Picture or Image in Excel Cell

You can easily insert a picture into a cell, and it will move, resize, and filter if the picture properties are set to Move and size with cells.

Please follow the below steps to Insert a Picture or Image in Excel

1. Select cell C2.
2. Go to the Insert tab.
3. Select the Illustrations command.
4. Click This Device option in the Pictures.

5. Select the picture Apple.png that you want to insert into a cell in Excel in the Insert Picture dialog box.
6. Click the Insert.

7. You can resize the image to adjust the Height and Width of the picture in the Size Group in the Picture Format Tab.

Lock Image or Picture in Excel

By default, the inserted picture or image does not stick with the cell. You have to select the Move and size with cells option in the properties item. if you need the image to stick to the cell.

Steps to lock an image or picture in a cell.

1. Select the Image, and Click the Right Mouse Button.
2. Select the Format Picture – to open the Format Picture pane.

3. Select Size & Properties in the Format Picture pane.
4. Choose the Move and size with cells property, you can see there are three options in the Properties, default Move but don’t size with cells is selected.

You follow the above steps to insert and format all other pictures. The output will look like the below image.

Insert Slicer in Excel

Excel Slicers are a filtering tool. It allows you to filter and view data in an Excel PivotTable or Table. You can select a single item or multiple items from a list of options in the slicer and instantly see the results in the Table or PivotTable.

To insert a slicer in Excel, first you want to convert the data as a table and insert the slicer from the Table Design Tab.

Steps to insert slicer in Excel.

1. Select the range of cells A1:C16
2. Press Ctrl + T or Click the Table command in Insert Tab

3. Click the Insert Slicer command in Table Design Tab – to open the Insert Slicer dialog box
4. Select the Category in the field list and Press OK.

Move the Category slicer to column E. You can select any one category or multiple categories in the list to filter your data.

Move and Size the Inserted Image with VBA

VBA Script can be used to dynamically move or resize the object based on the current target cell. You have picture properties to adjust the Left, Top, Height and Width of the picture.

Insert all the images and make sure the image names are aligned with the data in column A.  Macro refers to the cell value in Column A to select the image and align it in Excel. To rename the inserted image, just select the image and type the name in the Name box.

Sub movePictures()

' Declare variables
Dim DataRange As Range

' Assign selected range of cells
Set DataRange = Sheets("VBA").Range(Selection.Address)

' Loop through the selected range
For Each rg In DataRange
    
    ' Assign image name
    imgName = rg.Value
    
    ' Set inserted image Left and Top properties with cell
    ActiveSheet.Shapes.Range(Array(imgName)).Left = rg.Offset(0, 1).Left + 3
    ActiveSheet.Shapes.Range(Array(imgName)).Top = rg.Offset(0, 1).Top + 1
    
Next

End Sub

Add the above VBA Procedure to your VBE module.

Macro declares the variable DataRange and assigns the selected range of cells to the variable DataRange. In the iteration, the script set the current cell value to the variable imgName. The Offset function refers to the same row and one column to the right. Assign the cell left and top values to the respective image Left and Top properties.

You can use the object height and width properties to resize the height and width of the inserted image.

To run the movePictures macro. Press Alt + F8, the keyboard shortcut to open the Macro dialog box, Choose the movePictures Macro and then Press Run.

Move and Size the Inserted Image with Office Script

Office script is a programming script that allows you to loop through a selected range of cells and set an image, picture, or shape object’s properties.  Such as left, top, height, and width. It works in both desktop and web Excel versions.

Insert all the images and make sure the image names are aligned with the data in column A.  Script refers to the cell value in Column A to select the image and align it in Excel. To rename the inserted image, just select the image and type the name in the Name box

function main(workbook: ExcelScript.Workbook) {

  // get current worksheet
  let currentSheet = workbook.getActiveWorksheet();
  
  // get selected range
  let rng = workbook.getSelectedRange();

  // Store Number of Rows 
  let rows = rng.getRowCount();

  // loop through the selected range of cells
  for (let i = 0; i < rows; i += 1) {

    // Assign the cell value to the variable cellValue as a string
    let cellValue = rng.getCell(i, 0).getValue() as string;
    let currImage = currentSheet.getShape(cellValue);

    // Assign values Left and Top values to the respective variables
    let left = rng.getCell(i,1).getLeft()+3;
    let top = rng.getCell(i, 1).getTop()+1;
    
    // Move the image or picture 
    currImage.setLeft(left);
    currImage.setTop(top);

  } 
}

Script assigns the range of cells A2:A6 to the variable rng. Next, loop through each cell in the given range rng, and assign the current cell value to the variable cellValue. The getShape function assigns the image to the variable currImage. Store current cell left and top property values to the variables left and top. Finally set the image Left and Top properties.

You can use the functions setHeight and setWidth to set the Height and Width of an image

Follow the below steps to execute the Move or Size Picture Office script in your Excel.

Assuming that all the images are in the same sheet and the names are aligned with column A.

1. Select the range of cells A2:A6.
2. Go to Automate menu.
3. Select the Move or Size Picture Script – to open the Code editor on the right side of your Excel application.
4. Press Run 

Conclusions

Sometimes you want to insert an image, picture, or shape with data in Excel. Also, you may need to move or resize an inserted object dynamically for your dashboard or automation.

Suppose in your automation project, you have a list of countries in a dropdown control and inserted all country flags. You need to show, move, and resize the currently selected country flag in the header and hide all other flags. You can use the programming script to set the country flag Left, Top, Height and Width properties to move and resize.

The Format Picture feature in Excel allows you to adjust the size, position, and effects of the inserted image.

VBA or Offices Scripts code helps you to runtime move or resize inserted images, pictures, or shapes in Excel for your dashboard or report automation.

Have you tried to Lock the Pictures in Excel? Did you know any other way to do this? Let me know in the comments below!