You can accomplish what you want fairly simply, however it requires 2
columns of data for your list -- one with the text items and one with
the ordinals. Hopefully that requirement doesn't affect you too much.
Here are the steps:
1) Insert a combobox from the Control Toolbox (View menu --> Toolbars
--> Control ToolBox); i.e. Don't use the Forms toolbar.
2) Define your list of text items to use in the list in one column,
and then in the column adjacent to it on the right, define the
ordinals. For example, cells A1 to A5 might contain my 5 item names
and cells B1 to B5 would then contain the numbers 1, 2, 3, 4, 5.
3) right-click the combox and click "Properties..."
4) Set the "ListFillRange" property to the full cell range containing
BOTH the text list and the ordinals. Using my example from step 2, the
range I would type here would be A1:B5
5) Set the "LinkedCell" property to the cell where you want the
selected value (ordinal) to be displayed.
6) Change the "BoundColumn" property from 1 to 2. This tells Excel the
data you're binding to is from the 2nd column, which contains the
ordinals. In other words, the value that will be displayed in the
'LinkedCell' cell will be from the 2nd column.
7) Change the "TextColumn" property to 1. You don't really need to do
this, but this identifies the 'Text' property value, just as
"BoundColumn" identifies the 'Value' property value.
8) As you know, you can set your font information through the "Font"
property. You can set other appearance information too -- click the
"Categorized" tab and play with the properties under the "Appearance"
category.
Notes:
a) notice that we leave the "ColumnCount" property set to 1. If we set
it to 2, both the text and ordinals would display on each row in the
combobox list. With it set to 1, it still seems to recognize the whole
data set due to the "ListFillRange" property, it just will only
display the first column. The other alternative is to set the
"ColumnCount" property to 2 and then in the "ColumnWidths" property,
type "50,0" (which it will change to something like "49.95 pt; 0 pt".
The zero effectively hides the 2nd column (ordinals) while the 1st
column will be 50pt wide. You would have to use this approach if you
had your text and ordinal columns reversed so that you could specify
to only show the 2nd column.
Search Strategy: Just played with the Combobox settings in excel.
Looked up some of the specific property descriptions by googling for
property name + 'property' + 'excel', e.g. LinkedCell property
Excel
Hope this helps. |
