VBA & Macro Problems

How to Calculate Totals from Drop-Down Controls using Word VBA

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to calculate total numeric values from categorized drop-down content controls (such as hard and soft skills) using a VBA macro.

Product: Word
Device & OS: not provided
Scenario: Creating an interactive document where numeric totals automatically update based on the user's selections from multiple drop-down menus.
Observed behavior: The user placed drop-down controls in tables and text boxes but needs the correct VBA logic to extract and sum varying drop-down values based on specific categories.

Before you start

Ensure that the Developer tab is enabled in your Word ribbon, as you will need it to modify content control properties and access the Visual Basic editor.

Solution 1Recommended

Use the Document_ContentControlOnExit Event

Assign distinct tags to your content controls and use the OnExit event in the ThisDocument module to trigger a recalculation whenever a user changes a drop-down value and clicks away.

To calculate values dynamically, VBA needs a way to identify which drop-downs belong to which category. By assigning 'Tags' to content controls, your macro can easily filter and sum up the corresponding values.

The logic must be placed in the Document_ContentControlOnExit event. This ensures the macro runs automatically the moment a user finishes interacting with a specific drop-down control.

Assign Tags to the Drop-Downs

Select a drop-down content control in your document. Go to the Developer tab, click 'Properties', and enter a category identifier (e.g., 'HardSkills' or 'SoftSkills') into the Tag field.

Set Numeric Values for Options

In the same Content Control Properties window, add your drop-down list items. Make sure the 'Value' column for each item contains the actual number you want to add to the total, even if the 'Display Name' is text.

Open the VBA Editor

Press ALT + F11 on your keyboard to launch the Visual Basic Editor. In the Project Explorer pane on the left, double-click on 'ThisDocument'.

Write the OnExit Macro

Select 'Document' from the left drop-down above the code window, and 'ContentControlOnExit' from the right drop-down. Write a loop iterating through ActiveDocument.ContentControls, checking if the control's Tag matches your category, and adding its .Range.Text or value to a running total variable.

Additional Resource: For extensive examples of writing VBA code for content controls, Greg Maxey's Word Tips provides excellent reference material for handling drop-down selections and totals.

Advanced Form Automation

Create Automated Forms and Run Macros with WPS Writer

WPS Writer offers robust VBA and macro support, allowing you to easily build interactive documents, implement drop-down calculations, and process form fields just like you do in Microsoft Word.

1. Enable the Developer Tab: Go to settings to customize your ribbon and display the Developer tab to access macro tools.
2. Insert Form Controls: Use the Forms section within the Developer tab to insert drop-down lists into your document.
3. Write the VBA Macro: Click 'Visual Basic' to open the VBA editor and paste your custom calculation logic.
4. Save and Run: Test your drop-down totals and save the file as a Macro-Enabled Document.

Fully compatible with Microsoft Word macro-enabled formats (.docm)Supports native VBA for automating complex calculationsInsert and configure legacy form tools and drop-downs easilyLightweight, fast, and familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA calculating the drop-down totals automatically?

Make sure your VBA code is placed specifically inside the Document_ContentControlOnExit event within the ThisDocument module, not in a standard module. This specific event is required to trigger the code when the user finishes interacting with the control.

Can I calculate totals for different categories separately?

Yes. By assigning different text to the 'Tag' property of your content controls (for example, 'HardSkills' for some and 'SoftSkills' for others), your VBA loop can use an If statement to calculate completely separate totals based on those tags.

How do I calculate numbers when the drop-down displays text?

In the content control properties, set the 'Display Name' to the descriptive text the user will see, and set the 'Value' field to the corresponding number. Your VBA code should be written to extract and sum this numeric value.

Excel & Data Problems