How to Update Inventory Values in Excel Shapes with VBA
Question details
The user needs to correctly update an inventory value displayed inside an Excel shape using VBA after a user form adds product quantities.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user is tracking inventory and using a VBA user form to enter product data into a table. This action should automatically subtract the entered amount from total quantities shown inside specific worksheet shapes.
- Observed behavior
- The VBA script produces an error and fails to update the shape's text correctly, likely due to relying on the wrong event trigger or using unqualified worksheet references.
Ensure that macros are enabled in your workbook settings and make sure to identify the exact names of your target worksheet and shapes before modifying the VBA code.
Use the Worksheet_Change Event with Fully Qualified References
Switching from Workbook_SheetChange to Worksheet_Change and explicitly defining your worksheet and range references ensures the macro targets the correct shapes without errors.
Using the Workbook_SheetChange event can trigger your code across unintended sheets, causing referencing errors when updating shapes. Instead, placing the code in the specific Worksheet_Change event isolates the action to the sheet where the table is being updated.
Additionally, using methods like .Select or .Activate can slow down your macro and lead to runtime errors if the user switches sheets. Fully qualifying your references (e.g., stating exactly which sheet a range or shape belongs to) resolves this issue.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.
In the Project Explorer pane on the left, double-click the specific worksheet where your user form inputs the data (e.g., 'Grain Data'). Do not put this code in 'ThisWorkbook' or a standard module.
Create a Private Sub Worksheet_Change(ByVal Target As Range) block. Write logic to check if the 'Target' intersects with your inventory data table to ensure the macro only runs when relevant cells are updated.
When referencing the shape on the 'Inventory' sheet, write out the full path, such as: ThisWorkbook.Sheets("Inventory").Shapes("ShapeName").TextFrame.Characters.Text = new_value.
Include an 'On Error GoTo ErrorHandler' statement at the beginning of your code, and an 'ErrorHandler:' label at the end to catch unexpected inputs and gracefully exit the sub without crashing.

Easily Manage Macros and VBA with WPS Office
WPS Office provides robust, built-in support for VBA and macros, allowing you to seamlessly run your Excel scripts, update worksheet shapes, and automate your complex inventory workflows without compatibility issues.
- 1. Download and Install: Download WPS Office for free and install it on your computer.
- 2. Open Your Macro Workbook: Open your .xlsm inventory file using WPS Spreadsheet.
- 3. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on the 'VBA Editor' icon.
- 4. Edit Your Events: Paste or edit your Worksheet_Change code directly within the WPS VBA environment and run your automated shape updates seamlessly.

Frequently Asked Questions
Why does my VBA code fail when using Workbook_SheetChange?
The Workbook_SheetChange event fires whenever any cell on any sheet in the entire workbook is changed. If your code does not specifically check which sheet triggered the event, it may attempt to update a shape or read a value from the wrong sheet, resulting in a runtime error.
How do I find the exact name of a shape in Excel?
Click on the shape in your worksheet, then look at the Name Box located to the left of the formula bar. You can also rename the shape there to something easily recognizable for your VBA code, like 'InventoryShape1'.
Why should I avoid using .Select or .Activate in my VBA scripts?
Relying on .Select or .Activate slows down macro execution and makes the code prone to errors if the active window loses focus. Fully qualifying your references (e.g., Sheets("Sheet1").Range("A1").Value) allows VBA to read and write data in the background seamlessly.
How do I update the text inside a shape using VBA?
You can update a shape's text by targeting its TextFrame property. The standard syntax is: ActiveSheet.Shapes("YourShapeName").TextFrame.Characters.Text = "Your New Text".




