How to Populate WPS Office Excel Text Boxes from a Table with Formulas or VBA
Question details
The user wants to make Excel text boxes or picture boxes automatically display a changing list based on a table, using cell linking formulas or VBA instead of manually editing each shape.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A workbook contains a named table with role and responsibility columns, and separate shapes on destination sheets should display only the matching responsibilities.
- Observed behavior
- Text boxes and shapes do not update directly from table values; they remain static unless linked to worksheet cells or updated with VBA.
Make sure your source data is formatted as an Excel table or has a defined name, and identify the blank helper cells where the dynamic FILTER results can be placed before you link the shapes.
Link text boxes to dynamic FILTER helper cells
Use this formula-based method when you want shapes to update automatically without macros and when your source table uses structured references such as Table1[Role].
A helper range lets a formula return matching values to worksheet cells; each shape is linked to one helper cell through the formula bar, so the displayed text follows the cell value whenever the table changes.
On the destination sheet, select the first helper cell, such as A1, and enter =FILTER(Table1[Responsibility],Table1[Role]="Farmer",""). Press Enter to let results spill down.
Select the helper range, press Ctrl+1, and set the number format to Custom: ;;; so the worksheet stays clean without deleting formulas.
Select the rectangle or text box, click the formula bar, type =A1, and press Enter.
Select each remaining shape and enter =A2, =A3, and so on. When the table changes, FILTER returns shifted or blank results and the shapes update accordingly.
Use VBA to update shape text directly from the table
Use this method when you can save the workbook as macro-enabled and want a single macro to refresh shape text without a visible helper range.
Create dynamic auto-updating text boxes in WPS Office Excel
Use WPS Office Excel to link text boxes to FILTER helper cells, so your shapes automatically display matching rows from a table without manual edits. WPS keeps the same formula bar, shape-linking workflow, and .xlsx/.xlsm compatibility you expect.
- 1. Open your workbook in WPS Office Excel: Launch WPS Office, open the .xlsx workbook, and go to the sheet that contains the shapes and table.
- 2. Create the dynamic helper range: Enter a FILTER formula such as =FILTER(Table1[Responsibility],Table1[Role]="Farmer","") in the first helper cell.
- 3. Link each text box to a helper cell: Select each shape, click the formula bar, and type =A1, =A2, and so on to bind the text to the helper cells.
- 4. Test the update: Change a table value and confirm the linked shapes update automatically.
Frequently Asked Questions
Can a text box show a formula result directly without linking to a cell?
No. A text box or shape cannot hold a worksheet formula. Select the shape, click the formula bar, and link it to a helper cell such as =A1, or use VBA to update the shape.
Why does my shape text not update when the table changes?
The shape is likely not linked to a helper cell. Click the shape, check the formula bar for a reference like =A1, and make sure the helper cell contains the FILTER or INDEX formula.
How do I hide the helper formula cells from the printed sheet?
Select the helper cells, press Ctrl+1, go to the Custom category, and enter ;;; as the number format. Values are hidden but formulas still calculate.
Can I use this method without enabling macros?
Yes. The FILTER helper-cell method is macro-free. Use VBA only if you need direct shape updates and can save the workbook as .xlsm.
