Assign totals to combo box values?

[Balrajss0121]

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

 

[GWteB]

You could set up your sheet like

Book1
	A	B
1	Pizza	1
2	Pasta	2
3	Beer	3
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]

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.

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.

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.