Assign totals to combo box values?

Balrajss0121

New Member
Joined
May 6, 2020
Messages
11
Office Version
365, 2019, 2016
Platform
Windows
Hi, I have a question regarding combo boxes and totals. If for example, I had a combo box filled with menu items e.g. pizza, pasta, is it possible to assign each option with a numerical value? If a food item is clicked upon that number would then be added into a total cost cell. Any help would be appreciated, thanks :)
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
753
Office Version
2013
Platform
Windows
You could set up your sheet like
Book1
AB
1Pizza1
2Pasta 2
3Beer3
Sheet1

Set the RowSource property of the combo box to the desired range (eg "A1:B3"), set both BoundColumn and ColumnCount properties to 2.
Take the combo box Width property (for example 150), subtract 2 from it and use the result (eg 148) for the width of the first column. Set the width of the second column to 0, so the ColumnWidths property has to be set to 148 pt; 0 pt (for example).
ComboBox shows Pizza, Pasta, Beer but returns in its Value property 1, 2 or 3.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,565
I would use a two column list box. One column with menu items and the other with columns.
If you have menu items in column A and prices in column B, this code would fill the combobox.

VBA Code:
Private Sub UserForm_Initialize()
    With ComboBox1
        .ColumnCount = 2
        .TextColumn = 1
        .BoundColumn = 2
        '.ColumnWidths = ";0": Rem hide prices
        
        .List = (Range(Range("B1"), Cells(Rows.Count, 1).End(xlUp)).Value)
    End With
End Sub
Note the commented out line that would hide the prices in the dropdown.

Since the .BoundColumn = 2, the .Value of the combobox comes from the second column (i.e.) the price. Similarly the .Text property of the ComboBox is the menu item selected.

Addding the selected item to a total price cell could be done with code like this.
VBA Code:
Private Sub ComboBox1_Change()
    With Range("D1")
        .Value = Val(.Value) + Val(ComboBox.Value)
    End With
End Sub
But the difficulty in making corrections to mis-entry would lead me to make this a more than single click proceedure.

Code similar to this should work if you have an ActiveX combobox.
Also, if you decide to switch to ListBox instead of the ComboBox, the coding is the same.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,791
Messages
5,470,796
Members
406,724
Latest member
BruceLee Nguyen

This Week's Hot Topics

Top