Results 1 to 9 of 9

userform combobox error "invalid property value"

This is a discussion on userform combobox error "invalid property value" within the Excel Questions forums, part of the Question Forums category; One userform is designed to "deactivate" members but retain their associated data. This is handled by renaming them to zzFirst ...

  1. #1
    Board Regular TryingToLearn's Avatar
    Join Date
    Sep 2003
    Location
    NJ
    Posts
    692

    Default userform combobox error "invalid property value"

    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
    -Bob [XL2003 & 7 on XP] Slowly coming out of the Stone Age and trying to trade in my abacus.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    70,332

    Default Re: userform combobox error "invalid property value&quo

    Can you post your ComboBox1_Change code? On which line do you get the error?

  3. #3
    Board Regular TryingToLearn's Avatar
    Join Date
    Sep 2003
    Location
    NJ
    Posts
    692

    Default Re: userform combobox error "invalid property value&

    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.
    -Bob [XL2003 & 7 on XP] Slowly coming out of the Stone Age and trying to trade in my abacus.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    70,332

    Default 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?

  5. #5
    Board Regular TryingToLearn's Avatar
    Join Date
    Sep 2003
    Location
    NJ
    Posts
    692

    Default 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)
    -Bob [XL2003 & 7 on XP] Slowly coming out of the Stone Age and trying to trade in my abacus.

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    70,332

    Default Re: userform combobox error "invalid property value&quo

    What column is "Z other" in? And what's in E70?

  7. #7
    Board Regular TryingToLearn's Avatar
    Join Date
    Sep 2003
    Location
    NJ
    Posts
    692

    Default 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
    -Bob [XL2003 & 7 on XP] Slowly coming out of the Stone Age and trying to trade in my abacus.

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    70,332

    Default 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

  9. #9
    Board Regular TryingToLearn's Avatar
    Join Date
    Sep 2003
    Location
    NJ
    Posts
    692

    Default 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
    -Bob [XL2003 & 7 on XP] Slowly coming out of the Stone Age and trying to trade in my abacus.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com