Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ComboBox SetItemData type feature?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ComboBox SetItemData type feature?
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ComboBox SetItemData type feature?
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. 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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ComboBox SetItemData type feature?
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. 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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ComboBox SetItemData type feature?
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" wrote in message ... 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. 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" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ComboBox SetItemData type feature?
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. What exactly do you want to retrieve? The combobox has a Listindex which maybe you could use. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ComboBox SetItemData type feature?
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) "Mike Jones" wrote in message ... 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. What exactly do you want to retrieve? The combobox has a Listindex which maybe you could use. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Adding new 'Type' to Format->Number->Time->Type | Excel Discussion (Misc queries) | |||
Autofill type feature. | Excel Worksheet Functions | |||
How Do I Load A ComboBox RowSource From The Results Of Another ComboBox | Excel Programming | |||
Userforms: combobox.value type | Excel Programming |