userform combobox error "invalid property value"

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
730
One userform is designed to "deactivate" members but retain their associated data. This is handled by renaming them to zzFirst Lastname and resorting the list.

To re-activate them on another userform, the zz is taken off their name.
This form's combobox has a default value of "Name". A dynamic range finds names after a "tag" name of Z Other in the list. If the default Name is cleared by the user and there are no other names in the list, hitting any other control on the form returns: invalid property value
and I can't seem to get out of it other than resetting the code.

I've tried various error traps but it doesn't seem to get as far as the traps. I tried an error trap in the Private Sub ComboBox1_Change() to no avail either.

Properties include:
fmMatchEntryFirstLetter
matchrequired true

Stepping through it I can see it get to ComboBox1_Change() but it stops there and returns the invalid....

I'm assuming the code of the ok & cancel controls don't matter here.

Any suggestions?

TIA
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Re: userform combobox error "invalid property value&quo

Can you post your ComboBox1_Change code? On which line do you get the error?
 
Upvote 0
Re: userform combobox error "invalid property value&amp

Andrew:

There is no code at this time in the ComboBox1_Change code. I had tried error trapping in there but took it out.

I also tried a simple:

Private Sub MbrReact_Change()
On Error Resume Next
End Sub

No matter what I do, the error seems to come up on the
Private Sub MbrReact_Change() line itself. Never makes it past this.

It works fine as long as the default name is not changed or there is a valid name selected. I've also discovered that the dynamic range specifies 1 blank line in the possible range. If this blank line is selected, it works fine.

The error is showing up when:
1. user inputs a character (only 1 character is accepted - I assume because of the property set for fist letter match)
2. user deletes what is in the "default" field.

Can be corrected by selecting the blank range on the drop down but the average user isn't going to know that!

P.S. I tried, in the _Change code:

if combobox1.value="" then
combobox1.value="Name"
end if

but as I said, it never seems to make it pas the _change code line.
 
Upvote 0
Re: userform combobox error "invalid property value&quo

How are you filling your ComboBox - RowSource property? That's probably the cause of the error if the dynamic range is empty. Why not disable the ComboBox if there are no names in the list?
 
Upvote 0
Re: userform combobox error "invalid property value&quo

The command buttons also call this as a sub to reset the range after a change has been made.
code in INITALIZE that defines the range:

Dim othr as string
othr = Worksheets("data").Cells.Find(What:="Z other", LookIn:=xlFormulas, _
LookAt:=1, MatchCase:=False).Offset(1, 0).Address
rngTarget = "data!" & othr & ":$e$70"
ComboBox1.RowSource = rngTarget

To follow through on your suggestion, how can I test the range to see if there is any valid names? I would put an if statement before the last line of code above, but i'm not sure of the syntax to test the range.

if rngTarget = "???????" then
ComboBox1.Enabled=False
end if


(don't know if i can stay up this morning for a reply, but I greatly appreciate your help)
 
Upvote 0
Re: userform combobox error "invalid property value&quo

What column is "Z other" in? And what's in E70?
 
Upvote 0
Re: userform combobox error "invalid property value&quo

z other is in col E
E70 is the last cell that a name can be in
first cell for a name is e21
 
Upvote 0
Re: userform combobox error "invalid property value&quo

So if Z Other is in E69, the range address will be only E70. If that's a blank cell you can use:

Code:
If IsEmpty(Range("data!" & othr & ":$e$70")) Then
   ComboBox1.Enabled=False
End If

Or you could count the number of rows in the range:

Code:
If Range("data!" & othr & ":$e$70").Rows.Count = 1 Then
   ComboBox1.Enabled=False
End If
 
Upvote 0
Re: userform combobox error "invalid property value&quo

Andrew - thank you for sticking with me on this.

I tried both suggestions but it didn't return the hoped for result. It did get me on the track of this solution:

othr is the location of Z Other in col E
if the last cell used in that column is othr, then just don't show the userform in the first place.

Dim OTHR As String
Dim blankrng As String

OTHR = Worksheets("data").Cells.Find(What:="Z other", _ LookIn:=xlFormulas, LookAt:=1, MatchCase:=False).Address

blankrng = Sheets("data").Range("e65536").End(xlUp).Address

If blankrng = OTHR Then
MsgBox "There are no inactive Members", vbCritical
Exit Sub
End If

I'm sure there's an easier way to format this test, but it works! I see similar problems on my other comboboxes, but they all have data that can be selected to keep the flow goin...guess it's just an MS thingie.
I appreciate your assistance o_O
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top