VBA & Macro Problems

How to Return a Numeric Value from an Excel Combo Box

Steven Parker Oct 14, 2026 869 views

Question details

The user wants to display text labels (such as '100g' or '1Kg') in a combo box, but output a specific numeric value (like 0.09 or 0.99) to a worksheet cell based on the selection.

How to Return a Numeric Value from an Excel Combo Box

Product: Excel
Device & OS: not provided
Scenario: Creating an interactive form or dependent combo box where selected text items must map to specific numeric values for backend calculations.
Observed behavior: The combo box natively outputs the selected text label, but the goal is to dynamically look up and return a corresponding numeric value into a target cell.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that you have basic familiarity with navigating the VBA Editor and UserForms.

Solution 1Recommended

Use a Worksheet Lookup List and VBA AfterUpdate Event

Create a background lookup list in your worksheet and use a simple VBA script to fetch the numeric value when the combo box selection changes.

By leaving the ControlSource property of the combo box blank, you prevent it from automatically writing the text label to a cell. Instead, you can use the AfterUpdate event to intercept the selection, look up the text in a worksheet range, and push the corresponding number to your desired cell.

Create a lookup list

On a worksheet, create a two-column list. Enter your display values (e.g., '100g', '250g') in the first column, and the corresponding numeric values (e.g., 0.09, 0.25) in the second column. Name this range 'WeightList'.

Clear the ControlSource property

Open the VBA Editor, select your UserForm, and click on your Combo Box. In the Properties window, ensure the 'ControlSource' field is completely blank.

Access the AfterUpdate event

Double-click the Combo Box in the VBA editor to generate a code block. Change the event type in the top-right dropdown from 'Change' to 'AfterUpdate'.

Write the lookup VBA script

Inside the AfterUpdate sub, write a VLookup script. For example: 'Sheet1.Range("A1").Value = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Sheet2").Range("WeightList"), 2, False)'. This places the numeric result into cell A1.

Use a Worksheet Lookup List and VBA AfterUpdate Event

Handling dependent combo boxes: If this combo box depends on a previous selection (e.g., Large Letter vs. Parcel), you can use the Change event of the first combo box to dynamically update the RowSource or list items of this combo box before the lookup occurs.

Solution 2

Use a Hidden Column in a Multi-Column Combo Box

Store the numeric value directly inside the combo box using a hidden second column, eliminating the need for a worksheet lookup formula in VBA.

Advanced Spreadsheets with WPS Office

Easily Manage Combo Boxes and VBA Macros with WPS Spreadsheet

WPS Spreadsheet fully supports advanced Form Controls, UserForms, and VBA macros, allowing you to create complex interactive lookup lists and return numeric values seamlessly just like in Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled spreadsheet.
2. Access Developer Tools: Click on the Developer tab in the top ribbon to access Form Controls and the VBA environment.
3. Insert your Combo Box: Click the 'Insert' icon under Form Controls to draw a Combo Box directly onto your sheet or UserForm.
4. Edit VBA Code: Click 'Visual Basic' to open the editor, where you can paste your AfterUpdate lookup script to handle numeric values.

Excellent compatibility with Microsoft Excel .xlsm and .xlsb macro-enabled formatsFull support for Visual Basic for Applications (VBA) to automate complex data workflowsIntuitive Developer tab for easy management of Combo Boxes and Form ControlsFree, lightweight, and fast alternative for daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel Form Control Combo Box return an index number instead of a value?

If you insert a standard Form Control Combo Box (not ActiveX or UserForm) and link it directly to a cell, Excel natively returns the index position of the selected item (e.g., 1 for the first item, 2 for the second). To get the actual value, you must use an INDEX function in another cell, such as =INDEX(ListRange, LinkedCell).

How do I make a combo box dependent on another combo box's selection?

You can use the Change event of the primary combo box in VBA. When the first selection is made (e.g., 'Parcel'), the code clears the second combo box using ComboBox2.Clear and populates it with new conditional items using ComboBox2.AddItem.

Can I use the VLOOKUP function directly inside a VBA script?

Yes, you can call standard Excel worksheet functions within VBA by using Application.WorksheetFunction. For example, Application.WorksheetFunction.VLookup allows you to perform lookups on worksheet ranges directly within your combo box code.

Excel & Data Problems