Invalid Property Value error in UserForm combo box

[Dale Fye]

I have several combo boxes on a user form that I am using to populate cells
in a hidden worksheet. After one of these combo boxes gets the focus, I am
unable to move the focus to another control on the form if I don't select a
value from the combo box. This error also occurs after I have seleted a
value from the combo an then gone back and deleted it.

The dialog box does not indicate a particular error number, and only gives
the OK button to exit. To move my cursor out of the control, I have to
escape out, which voids the other changes I have made to that "record".

I'm sure there is a property (AllowNulls) or something like that that I am
missing on these combo boxes, would someone please enlighten me?

Dale

 

[Dale Fye]

Let me clarify.

I found the MatchRequired and Style properties, and I really want to keep
the MatchRequired set to true.

Is there a way to allow a blank entry in a field and still keep
MatchRequired as true?

I know I can change MatchRequired to false, and then write some code for the
AfterUpdate of the combo to test to see whether the entered value is in the
list that populated the combo, but would like to avoid this extra coding if
possible.

Thanks

 

[Chip Pearson]

You can set the MatchEntry proprety to 1 - Match Entry Complete and then put
an empty value at the top of the list.

With Me.ComboBox1
.AddItem ""
.AddItem "AAA"
.AddItem "ABC"
.AddItem "BBB"
.AddItem "BCD"
.AddItem "BCCEE"
End With

I believe this will allow you to do what you want.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Dale Fye]

Thanks, Chip.

I've been working with Access for so many years, I'm going to have to train
myself to think "outside the box" with Excel.

 

[Dale Fye]

Chip,

That worked for initial data entry, but if I want to delete an entry, and
leave it blank, that technique does not work.

Dale

 

[Chip Pearson]

Dale,

I'm not sure if I understand. I think I was mistaken on the Match Entry
setting. It should be 1 - frmMatchEntryComplete. Also, you'll want to set
AutoWordSelect to False and Style to 2 - frmStyleDropDownList.

As long as you don't delete the empty string element of the list, it should
work even if you delete an item from the list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

Chip,

That worked for initial data entry, but if I want to delete an entry, and
leave it blank, that technique does not work.

Dale

 

[Dale Fye]

Chip,

Appreciate your help, but I finally went with the custom validation function
I previously mentioned. What I wanted was a way to skip one of the combo
boxes, but also wanted to be able to delete from the box, if the user changed
their mind and wants to leave that entry blank. I tried changing the style
to a DropDownList but that was balking when my code tried to fill it in with
an empty string.

My current settings are:
MatchEntry: 1 fmMatchEntryComplete
MatchRequired:False
Style: 0 - frmStyleDropDownCombo

Then, in the AfterUpdate event of each combo I have code that lets an empty
string pass and checks to see if the entered value IsInList(Value, Range).
If it fails both of these tests, it displays an error message and returns
control to the combo box.

Public Function IsInList(SomeValue As String, ListRange As String)

Dim rng As Range
Dim intMatch As Integer

'On Error GoTo ErrorHandler

Set rng = Worksheets("Settings").Range(ListRange)

On Error Resume Next
intMatch = WorksheetFunction.Match(SomeValue, rng, 0)
If Err.Number = 0 Then
IsInList = True
Else
IsInList = False
End If

Set rng = Nothing

End Function