Hi,

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

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

To give a better idea of the overall picture, imagine a spreadshee
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 name
range to look up the correct values.

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

Thanks in advance,
Vindalo

--
Vindalo
-----------------------------------------------------------------------
Vindaloo's Profile: http://www.excelforum.com/member.php...fo&userid=3263
View this thread: http://www.excelforum.com/showthread.php?threadid=52446

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

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

Regards,
Vindalo

--
Vindalo
-----------------------------------------------------------------------
Vindaloo's Profile: http://www.excelforum.com/member.php...fo&userid=3263
View this thread: http://www.excelforum.com/showthread.php?threadid=52446