Combo Box mandatory selection

[Vindaloo]

Hi,

I have a Combo Box (added through the Control Toolbox) that I want to be mandatory, i.e. it must always contain one of the values referenced in the ListFillRange. Currently I can select one of the values, but I can then overtype it with something else not in the list, or remove it completely.

What is the best way to achieve this? I can do it easily using a Combo Box from the Forms toolbar but I want to do some additional processing on selection of a valid entry so I really need the Combo_Change subroutine.

To give a better idea of the overall picture, imagine a spreadsheet that has the Combo Box with values "Price List A" and "Price List B". According to this selection, a named range will be amended using:

Please Login or Register  to view this content.

All subsequent lookup functions in the sheets can then use this named range to look up the correct values.

What I want to avoid is the user typing in a random value or a blank that the above code can't handle (ignoring for the moment the use of a catch-all Else at the end)

Thanks in advance,
Vindaloo

[Tom Ogilvy]

change properties

matchentry FmMatchEntryNone
style fmStyleDropDownList

then the user must select from the list.

--
Regards,
Tom Ogilvy


"Vindaloo" wrote:

>
> Hi,
>
> I have a Combo Box (added through the Control Toolbox) that I want to
> be mandatory, i.e. it must always contain one of the values referenced
> in the ListFillRange. Currently I can select one of the values, but I
> can then overtype it with something else not in the list, or remove it
> completely.
>
> What is the best way to achieve this? I can do it easily using a Combo
> Box from the Forms toolbar but I want to do some additional processing
> on selection of a valid entry so I really need the Combo_Change
> subroutine.
>
> To give a better idea of the overall picture, imagine a spreadsheet
> that has the Combo Box with values "Price List A" and "Price List B".
> According to this selection, a named range will be amended using:
>
> Code:
> --------------------
> If Combo.Value = "A" Then
> ActiveWorkbook.Names("pricelist").RefersTo = "=prices!$A$1:$B$10"
> ElseIf Combo.Value = "B" Then
> ActiveWorkbook.Names("pricelist").RefersTo = "=prices!$D$1:$E$10"
> End If
>
> --------------------
>
> All subsequent lookup functions in the sheets can then use this named
> range to look up the correct values.
>
> What I want to avoid is the user typing in a random value or a blank
> that the above code can't handle (ignoring for the moment the use of a
> catch-all Else at the end)
>
> Thanks in advance,
> Vindaloo
>
>
> --
> Vindaloo
> ------------------------------------------------------------------------
> Vindaloo's Profile: http://www.excelforum.com/member.php...o&userid=32634
> View this thread: http://www.excelforum.com/showthread...hreadid=524461
>
>

[Vindaloo]

Thanks very much Tom. Next time I'll have a proper look through the properties!

Regards,
Vindaloo