How to set up conditional formatting in Combobox list?

[DaveHarding]

I want to change the text in a Combobox list if text is typed in rather than
being chosen fom the list. Basically i want to be able to identify if someone
has not picked from the drop down list

 

[Chip Pearson]

Set the Style property to 2 - fmStyleDropDownCombo. To allow only
items in the list to be selected, set the Style to 1 -
fmStyleDropDownList.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[DaveHarding]

Chip,

I already have Style property set to 2 - fmStyleDropDownCombo so that text
can be typed in or selected from the list but i want to identify when someone
enters text that is not already in the list. Is there some code/a way of
highlighting when the text entered is typed rather than chosen from the list.
I thought something like 'Conditional Formatting' where the background is
highlighted in say Red when the value entered does not appear in the drop
down list.

Thanks for any help you can offer.

Dave

 

[Chip Pearson]

First things first. Are you using a combobox on a user form or
directly on a worksheet? If on a worksheet, are you using the combobox
from the Controls commandbar or the Forms command bar?

If you are using the combobox directly on the sheet and it is the
combobox from the Controls command bar, do the following.

Set the ListFillRange property to the range that contains the values
to be listed in the combobox, say A1:A10. Set the LinkedCell property
to some other cell, say H1. Then, in some cell, enter the formula

=IF(ISERROR(MATCH(H1,A1:A10,0)),"not in list","in list")

If the selected item in the combobox comes from the dropdown list, the
cell will display "in list". If the item does not come from the
dropdown list, the cell will display "not in list".

For more effect, you could change "not in list" to FALSE and "in list"
to TRUE and then use Conditional Formatting to highlight the cell in
red if the item is not in the list.

If you are using a combobox on a user form, try code like the
following:


Private Sub ComboBox1_Change()
Dim S As String
Dim B As Boolean
Dim N As Long
With Me.ComboBox1
S = .Text
For N = 0 To .ListCount - 1
If StrComp(S, .List(N), vbTextCompare) = 0 Then
B = True
Exit For
End If
Next N
End With
If B = True Then
Me.Label1.Caption = "In List"
Else
Me.Label1.Caption = "Not In List"
End If
End Sub

When the user changes the value of ComboBox1, the text of the combobox
is tested against each element in the List. If found, the Caption of
Label1 gets "In List". If not found in the list, Label1 gets "Not In
List".



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[DaveHarding]

Chip,

The formula worked brilliant.

Thanks for your help.

Dave

P.s. Had a look at your website, very useful. I'll definately be looking
there again.