Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assigning values to a word in a drop down box
Is there a way to assign a different value to each option in a drop down box
so that when each selection is made in a form you can make a calculation? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assigning values to a word in a drop down box
When I need something like this, I usually create a two-column dropdown and set the width of the second column to 0 so it is not visible. Then, put the items in the first column and their associated values in the second column. You can then read the second column to get the value associate with the selected item. In the following example, CommandButton1 loads a two-column ComboBox with some sample data and CommandButton2 gets the selected item and its associated value from the ComboBox. Private Sub CommandButton1_Click() '''''''''''''''''''''''''''''''''''''''' ' Load up the array with some values. '''''''''''''''''''''''''''''''''''''''' Dim Arr(1 To 3, 1 To 2) Arr(1, 1) = "Item1" Arr(1, 2) = "11" Arr(2, 1) = "Item2" Arr(2, 2) = "22" Arr(3, 1) = "Item3" Arr(3, 2) = "33" With Me.ComboBox1 '''''''''''''''''''''''''''''''''''''''''''' ' Set 2 columns and hide the second column. '''''''''''''''''''''''''''''''''''''''''''' .ColumnCount = 2 .ColumnWidths = CStr(.Width * 0.85) & ";0" ''''''''''''''''''''''''''''''' ' Load the list with the array. ''''''''''''''''''''''''''''''' .List = Arr End With End Sub Private Sub CommandButton2_Click() Dim ItemName As String Dim ItemValue As Double ''''''''''''''''''''''''''''''''''''''''''' ' Get the seleted item from column 0 ' and the associated value from column1. ''''''''''''''''''''''''''''''''''''''''''' With Me.ComboBox1 If .ListIndex 0 Then ItemName = .List(.ListIndex, 0) ItemValue = CDbl(.List(.ListIndex, 1)) MsgBox "Item: " & ItemName & vbCrLf & _ "Value: " & CStr(ItemValue) Else MsgBox "No Item Selected" End If End With End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Aleyna0515" wrote in message ... Is there a way to assign a different value to each option in a drop down box so that when each selection is made in a form you can make a calculation? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning point values? | Excel Worksheet Functions | |||
Assigning values to drop-down list options | Excel Discussion (Misc queries) | |||
Help assigning a number to a word | Excel Discussion (Misc queries) | |||
Assigning values to letters | Excel Worksheet Functions | |||
assigning values by intervals | Excel Worksheet Functions |