Modifying drop-down box options and formats

[RiotLoadTime]

To fill a cell, I want the input user to have 7 options in a drop-down box, plus the potential for them to type whatever they want (as many letters as they want) if their response isn't listed. Also, in the drop down box, I want to offer him different text choices, but once he picks one of the text choices, I want a number to be saved in the cell. For example, here are the options, followed by the number I'd like to be recorded in the cell (in parenthesis):

Event (1)
Violation (2)
Decision (3)
Termination (4)
Opportunity (5)
Effect (6)
Pregnancy (7)


I've figured out how to make drop down boxes, but I have no idea how to allow for the user to type whatever he'd like if he can't find what he wants in the options, and I'm not sure how to record the number instead of the text. Finally, how do you enlarge the width of the drop-down box when your clicking through your options? The box is never any wider than the cell, and some of my options are full sentences (the ones above are just the first word of the actual options). I'd prefer not to make the cell width huge, since I have to do this for multiple cells and each one would end up being about half the screen. Any advice?

Thanks,
-RiotLoadTime

[Gord Dibben]

First of all, how did you make the drop-down boxes?

If using Data Validation List, you can allow entries not on the list by turning
off the Error Alert.

You would need event code to have a number appear in the cell rather than the
text.

You cannot format DV drop-downs for width. Limited to width of the cell.

Alternative to DV drop-downs are Combo Boxes from the Control Toolbox.

See Debra Dalgleish's site for working with DV dropdowns and Combo Boxes.

http://www.contextures.on.ca/tiptech.html

Scroll down to Data Validation tips index.


Gord Dibben MS Excel MVP

On Tue, 1 Aug 2006 10:19:19 -0400, RiotLoadTime
<[email protected]> wrote:

>
>To fill a cell, I want the input user to have 7 options in a drop-down
>box, plus the potential for them to type whatever they want (as many
>letters as they want) if their response isn't listed. Also, in the
>drop down box, I want to offer him different text choices, but once he
>picks one of the text choices, I want a number to be saved in the cell.
>For example, here are the options, followed by the number I'd like to
>be recorded in the cell (in parenthesis):
>
>Event (1)
>Violation (2)
>Decision (3)
>Termination (4)
>Opportunity (5)
>Effect (6)
>Pregnancy (7)
>
>
>I've figured out how to make drop down boxes, but I have no idea how to
>allow for the user to type whatever he'd like if he can't find what he
>wants in the options, and I'm not sure how to record the number instead
>of the text. Finally, how do you enlarge the width of the drop-down box
>when your clicking through your options? The box is never any wider
>than the cell, and some of my options are full sentences (the ones
>above are just the first word of the actual options). I'd prefer not
>to make the cell width huge, since I have to do this for multiple cells
>and each one would end up being about half the screen. Any advice?
>
>Thanks,
>-RiotLoadTime

[Gord Dibben]

First of all, how did you make the drop-down boxes?

If using Data Validation List, you can allow entries not on the list by turning
off the Error Alert.

You would need event code to have a number appear in the cell rather than the
text.

You cannot format DV drop-downs for width. Limited to width of the cell.

Alternative to DV drop-downs are Combo Boxes from the Control Toolbox.

See Debra Dalgleish's site for working with DV dropdowns and Combo Boxes.

http://www.contextures.on.ca/tiptech.html

Scroll down to Data Validation tips index.


Gord Dibben MS Excel MVP

On Tue, 1 Aug 2006 10:19:19 -0400, RiotLoadTime
<[email protected]> wrote:

>
>To fill a cell, I want the input user to have 7 options in a drop-down
>box, plus the potential for them to type whatever they want (as many
>letters as they want) if their response isn't listed. Also, in the
>drop down box, I want to offer him different text choices, but once he
>picks one of the text choices, I want a number to be saved in the cell.
>For example, here are the options, followed by the number I'd like to
>be recorded in the cell (in parenthesis):
>
>Event (1)
>Violation (2)
>Decision (3)
>Termination (4)
>Opportunity (5)
>Effect (6)
>Pregnancy (7)
>
>
>I've figured out how to make drop down boxes, but I have no idea how to
>allow for the user to type whatever he'd like if he can't find what he
>wants in the options, and I'm not sure how to record the number instead
>of the text. Finally, how do you enlarge the width of the drop-down box
>when your clicking through your options? The box is never any wider
>than the cell, and some of my options are full sentences (the ones
>above are just the first word of the actual options). I'd prefer not
>to make the cell width huge, since I have to do this for multiple cells
>and each one would end up being about half the screen. Any advice?
>
>Thanks,
>-RiotLoadTime