VBA & Macro Problems

How to Create an Excel Inventory Lookup with Part Numbers and Pictures

Khadija Khan Oct 16, 2026 869 views

Question details

The user wants to build an Excel inventory tool that can search by internal or manufacturer part numbers to automatically display corresponding product descriptions and images.

Create an Excel Inventory Lookup with Part Numbers and Pictures

Product: Excel
Device & OS: not provided
Scenario: Building a customized and dynamic inventory lookup system to quickly retrieve product visual and text details.
Observed behavior: The desired functionality exceeds the capability of standard spreadsheet formulas alone, requiring the use of form controls and VBA scripts to process searches and load pictures dynamically.

Before you start

Ensure you have enabled the Developer tab in your spreadsheet software to access form controls, and verify that all product images are stored systematically in an accessible local directory.

Solution 1Recommended

Use Form Controls and VBA Macros for Dynamic Image Lookup

Implement a customized search box or command button linked to a VBA script to search for part numbers and render corresponding product images.

Standard Excel formulas like VLOOKUP or XLOOKUP are excellent for retrieving text or numeric data, but they cannot inherently pull and display dynamic picture objects. To achieve a comprehensive inventory search tool with visuals, utilizing Form Controls alongside Visual Basic for Applications (VBA) is necessary.

This approach allows you to create an interactive dashboard where entering a part number triggers a script to locate the item's row and load its associated external or embedded image file directly into the interface.

Enable the Developer Tab

Go to File > Options > Customize Ribbon and check the 'Developer' option in the right-hand pane to make macro tools visible.

Insert Form Controls

Navigate to the Developer tab, click 'Insert', and select a Text Box (for the search input) and a Command Button (to trigger the search) from the ActiveX Controls section.

Set Up an Image Control

From the same 'Insert' menu under the Developer tab, add an Image Control to your sheet. This will serve as the container where the product pictures will be displayed.

Write the Lookup VBA Code

Press ALT + F11 to open the VBA Editor. Double-click the sheet containing your controls and write a script for the Command Button's 'Click' event that searches the part number database, retrieves the image path, and uses 'Image1.Picture = LoadPicture(filePath)' to display it.

Use Form Controls and VBA Macros for Dynamic Image Lookup

VBA Community Resources: If you need specific code snippets to match your exact worksheet layout, consider browsing or posting your requirements in the official Office VBA community.

Build Inventory Lookups in WPS

Create Dynamic Inventory Tools with WPS Spreadsheet

WPS Spreadsheet provides powerful built-in developer tools and comprehensive VBA support, making it simple to build interactive inventory lookup systems with dynamic picture integration.

1. Open Your Inventory File: Launch WPS Spreadsheet and open your existing inventory database.
2. Access Developer Tools: Navigate to the Developer tab on the top ribbon and insert your desired text box, button, and image controls.
3. Edit the VBA Script: Click the 'Macros' or 'Visual Basic' icon (ALT+F11) to open the VBA editor and script your image lookup logic.
4. Save as Macro-Enabled: Go to File > Save As and choose the '.xlsm' format to ensure your macro-driven lookup tool functions properly on subsequent opens.

Seamlessly compatible with Microsoft Excel (.xlsx, .xlsm) formats and macros.Intuitive built-in developer tab for inserting ActiveX controls easily.Robust VBA engine for executing dynamic image loading scripts without lag.Lightweight design ensuring fast performance even with large inventory datasets.

Frequently Asked Questions

Can I use standard formulas like VLOOKUP to return an image without VBA?

Formulas alone cannot output floating images. However, a common workaround involves using the Linked Picture feature combined with Named Ranges and the INDIRECT function. This dynamically updates an image based on a cell's value, though it becomes tedious to manage for very large inventories.

Why is my VBA script returning a 'File Not Found' error when looking up an image?

This usually occurs if the file path constructed in your VBA code does not match the actual location of the image on your computer, if the part number cell contains invisible trailing spaces, or if the image extension (e.g., .jpg vs .png) is incorrect.

How do I make the loaded image automatically fit the designated box?

In your VBA Editor, select your Image Control and adjust the properties window. Set the 'PictureSizeMode' property to '1 - fmPictureSizeModeStretch' or '3 - fmPictureSizeModeZoom' to ensure the image scales to fit the control boundaries automatically.

Excel & Data Problems