How can I distinguish the text displayed in a listbox from a real value.?

Question

I have a listbox with multiselect options. I populate it using the addItem function. I can't find any article about this on Google, but I need to distinguish from the text displayed in the listbox and a real value.

For example:

shown      hiddenvalue
--------   -----------
monday     A1
tuesday    A2
wednesday  C7

etc.

Is it possible? How can I access these values?

Answer 1

For VBA listbox you want to:

1. Declare two columns (ColumnCount = 2).
2. Make the second one hidden: ColumnWidths = ";0".
3. Declare the second column as bound (BoundColumn = 2) and the first column as textual (TextColumn = 1).

4. Have a procedure to add values:

   Private Sub AddWithID(Text As String, ID As String)
     ListBox1.AddItem Text
     ListBox1.List(ListBox1.ListCount - 1, 1) = ID
   End Sub
   

Now, for single-select listbox, you can use .Value or .Text to find out selected value/text.

For multi-select listbox, you can use .List(i, 0) for text and .List(i, 1) for value, where i in an index of a row.

Answer 2

Another way... Using Collections.

Private HiddenValue As New Collection

Private Sub CommandButton1_Click()
    AddItems "monday", "A1"
    AddItems "tuesday", "A2"
    AddItems "wednesday", "C7"
End Sub

Private Sub CommandButton2_Click()
    MsgBox "Shown Value :" & ListBox1.List(ListBox1.ListIndex) & vbNewLine & _
    "Hidden Value " & HiddenValue(ListBox1.ListIndex + 1)
End Sub

Private Sub AddItems(Text As String, ID As String)
    ListBox1.AddItem Text
    HiddenValue.Add ID
End Sub

SNAPSHOTS