OLE Combobox MatchRequired property not working

[Michael Deathya]

Hi,

I am using OLE comboboxes on my worksheets in Excel 97

I am having trouble w­ith the MatchRequired property.

I set it to true for one of my comboboxes but it doesn't app­ear to do
anything. The user is still able to click in the combobox, type
whatever ­they want and then move on. I need to restrict it to the
values in the ListFillRange.

How is it supposed to behave when MatchReq­uired=True?
Thanks,

Michael Deathya

 

[Tom Ogilvy]

the help says:

Specifies whether a value entered in the text portion of a ComboBox must
match an entry in the existing list portion of the control. The user can
enter non-matching values, but may not leave the control until a matching
value is entered.

AND

If the MatchRequired property is True, the user cannot exit the ComboBox
until the text entered matches an entry in the existing list. MatchRequired
maintains the integrity of the list by requiring the user to select an
existing entry.

-------
however after that it says:

Note Not all containers enforce this property.

So perhaps the OleObject container does not. I haven't tested it.

You could change the style of the combobox and force the user to select from
the dropdown list.

--
Regards,
Tom Ogilvy


Hi,

I am using OLE comboboxes on my worksheets in Excel 97

I am having trouble w­ith the MatchRequired property.

I set it to true for one of my comboboxes but it doesn't app­ear to do
anything. The user is still able to click in the combobox, type
whatever ­they want and then move on. I need to restrict it to the
values in the ListFillRange.

How is it supposed to behave when MatchReq­uired=True?
Thanks,

Michael Deathya

 

[Ron de Bruin]

Hi Michael

You must change the "Style" to dropdownlist in the properties

See the help for MatchRequired

Specifies whether a value entered in the text portion of a ComboBox must match an entry in the existing list portion of the control.
The user can enter non-matching values, but may not leave the control until a matching value is entered.



--
Regards Ron de Bruin
http://www.rondebruin.nl



Hi,

I am using OLE comboboxes on my worksheets in Excel 97

I am having trouble w­ith the MatchRequired property.

I set it to true for one of my comboboxes but it doesn't app­ear to do
anything. The user is still able to click in the combobox, type
whatever ­they want and then move on. I need to restrict it to the
values in the ListFillRange.

How is it supposed to behave when MatchReq­uired=True?
Thanks,

Michael Deathya

 

[Michael Deathya]

Thank you to both Ron and Tom (what is the correct etiquette when two
people give a solution?).

I have tried to do this programmatically using this code (adapted from
Tom or Chip at some point) but I get RT error 438: Object doesn't
support this method or property at the line where .Style is set.

Private Sub SheetControls()
For Each obj In ActiveSheet.OLEObjects
Select Case TypeName(obj.Object)
Case "TextBox"
iflag = 1
Case "CheckBox"
iflag = 2
Case "ListBox"
iflag = 3
Case "ComboBox"
iflag = 4
obj.Style = fmStyleDropDownList
'obj.Style = 2 ' numeric doesn't work either
Case "OptionButton"
iflag = 5
Case "ToggleButton"
iflag = 6
Case "ScrollBar"
iflag = 7
Case "Label"
iflag = 8
Case "SpinButton"
iflag = 9
Case "CommandButton"
iflag = 10
Case Else
iflag = 0
End Select
If iflag <> 0 Then
obj.Visible = True
End If
End Sub

Thanks,
Michael D

 

[Tom Ogilvy]

Private Sub SheetControls()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.ComboBox Then
obj.Object.Style = 2
End If
Next
End Sub

worked fine for me.