VBA ComboBox SetItemData type feature?

[Mike Jones]

I'm used to listboxes/comboboxes in C++ with an extra data holder than
you can set with SetItemData(). Is there something analogous in XL VBA
comboboxes? I hate to keep this in a separate array, because those tend
to get out of sync!

For example, I want a combobox that holds the name of an entity, but
behind the scenes, hold a unique id that properly identifies that entity.

 

[Guest]

So if I understand your question you want to set up a user defined data type?
This is right out of the help in VBA...

Type Statement Example
This example uses the Type statement to define a user-defined data type. The
Type statement is used at the module level only. If it appears in a class
module, a Type statement must be preceded by the keyword Private.

Type EmployeeRecord ' Create user-defined type.
ID As Integer ' Define elements of data type.
Name As String * 20
Address As String * 30
Phone As Long
HireDate As Date
End Type
Sub CreateRecord()
Dim MyRecord As EmployeeRecord ' Declare variable.

' Assignment to EmployeeRecord variable must occur in a procedure.
MyRecord.ID = 12003 ' Assign a value to an element.
End Sub

My favorite way to store UDT's is with a collection. You can add items,
remove items and edit the collections, accessing each item using its own
unique Key...

Look up collection in the help and you will be off to the races...

HTH

 

[Mike Jones]

I must have written my question poorly!

I want to put items in a ComboBox. When I select those items, I want to
be able to retrieve a useful number, rather than the text of the
combobox. Does the combobox contain a list of objects? Is there any
way that I can stored a special custom value or something with each line
item of the combobox?

I would strongly prefer NOT to keep this data in a separate array; that
always ends up being a kludge.

 

[Mike Jones]

I must have written my question poorly!

I want to put items in a ComboBox. When I select those items, I want to
be able to retrieve a useful number, rather than the text of the
combobox. Does the combobox contain a list of objects? Is there any
way that I can stored a special custom value or something with each line
item of the combobox?

I would strongly prefer NOT to keep this data in a separate array; that
always ends up being a kludge.

 

[Bob Phillips]

What exactly do you want to retrieve? The combobox has a Listindex which
maybe you could use.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mike Jones]

In this case, I want to retrieve company names and their associated ID's
from the a database. It is a lot cleaner to be able to do other queries
based on the ID, because I can get to other tables directly. Example:
ask for all employees in a company by ID--the company name, of course,
isn't in the Employee table).

I think I've figured out my answer: you use multicolumns and put your
data in one of the columns, then hide it. Not too great, but it'll work
(I think).

Seems like people would run into this problem all the time.

 

[Bob Phillips]

Yes, that was my next suggestion. If you set the columnwidth equal to the
combo width it gets hidden, but there is still a horizontal scrollbar.

--

HTH

RP
(remove nothere from the email address if mailing direct)