Value in Combobox to be limited to range

[kosherboy]

Hello.
In my userform i have a combobox that refers to a range in one of my sheets.
How could i control the combobox that only values that exist in the range that it refers to can be added to the combobox? Meaning, if i were to enter a value that does not exist in the range then a message box should pop up and the entry should be deleted.

Thanks in advance.

[6StringJazzer]

When you say the combobox "refers to a range" do you mean that the property RowSource refers to a range? If you change the Style property to fmStyleDropDownList, then only values from that range are available for selection and the user cannot type in anything different.

[kosherboy]

Hi Jeff,
Sorry, i was not specific. The combobox is in a userform and when the userform is called a code automatically fills populates the dropdown from a range.
i know there is an option in the combobox properties which forces the use to only choose from the dropdown but it won't let me enter a value of zero. Also, i really do want a message box to pop up when the user enters a value that does not match the range.

Thanks.

[6StringJazzer]

In the ComboBox_Change sub, you could add something like this, where valuerange is the Range that you use to populate the combobox.

Please Login or Register  to view this content.

I'm still not sure I understand what your goal is from a user interface standpoint. It is preferable to prevent a user from entering invalid data to start with as opposed to detecting it and forcing the user to correct it. And I'm not sure how entering a value of zero comes into play, if it's not an allowed range.

[kosherboy]

Hi Jeff,
I'm sorry again but i meant that if i select the match only function under properties it doesn't let me leave the combobox clear. Meaning, the code opens the userform and displays the combox as blank, but once i pick a value from the dropdown and then decide i don't want the value anymore excel won't let me revert to keeping the combobox blank and will force me to enter one of the values from the dropdown.

Also, i tried your code but it got bugged by the 2nd line.