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)
