Using Code/Display tables in Excel

[BooBoo]

I need to display a value to the users and when the users selects a
display value, I need to save a different code value to the file. In a
spreadsheet, the user will select frequency. I want to display text
for the code value.

For example, using the following table:

Code Display
1 Annual
2 Biannual
12 Monthly
52 Weekly
26 Biweekly
365 Daily
99 Other

I want to display annual, weekly, etc to the user. When the user
selects one of these values, I want to save the corresponding code
value to the file.

How can I do this?

 

[Joerg]

Here is a starting point:
1) From the control toolbox, select a list box and place it on the worksheet
2) Put your Code Display data into 2 columns (preferably somewhere "out of
sight", eg. X1:Y7)

Now set the properties of your list box:
3) ListFillrange: X1:Y7 (this populates your list)
4) ColumnCount:2
5) BoundColumn:1 (i.e. the value of the first column.This value that will go
into your sheet)
6) LinkedCell: that's the cell address where the selected value will be
placed

If you want your user to select "Monthly" and not "12 Monthly", you will
have to set the ColumnWidths to something like 0;5 (you'll have to find out
what the correct value for the second column is, but as long as the column
width of the first column is zero, it will not appear in the list box)

Cheers,

Joerg

 

[BooBoo]

Thanks!

Here is a starting point:
1) From the control toolbox, select a list box and place it on the worksheet
2) Put your Code Display data into 2 columns (preferably somewhere "out of
sight", eg. X1:Y7)

Now set the properties of your list box:
3) ListFillrange: X1:Y7 (this populates your list)
4) ColumnCount:2
5) BoundColumn:1 (i.e. the value of the first column.This value that will go
into your sheet)
6) LinkedCell: that's the cell address where the selected value will be
placed

If you want your user to select "Monthly" and not "12 Monthly", you will
have to set the ColumnWidths to something like 0;5 (you'll have to find out
what the correct value for the second column is, but as long as the column
width of the first column is zero, it will not appear in the list box)

Cheers,

Joerg