none
How to fix "Invalid Property Value" error message RRS feed

  • Question

  • Hello,

    I've built this userform and whenever I click on the command button to unload the entered information into the spreadsheet I get the "Invalid Property Value" message and the userform freezes with all the information cleared except for the listbox choices which are still highlighted.

    However, when checking the spreadsheet all the fields were populated accurately. 

    Can't seem to figure out why this message is coming up and why it is preventing the form from clearing for the next entry. Am I missing code for the listboxes that would allow for the information to clear when I unload the form?

    Here's the code that I have used, courtesy of Contexture and Bernie Dietrick.

    Thank you!

    Private Sub cmdAdd_Click()
    Dim lRow As Long
    Dim lPart As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Database")

    'find first empty row in database
    lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

    'check for a category
    If Trim(Me.cboCategory.Value) = "" Then
      Me.cboCategory.SetFocus
      MsgBox "Please enter category"
      Exit Sub
    End If

    'copy the data to the database
    With ws
      .Cells(lRow, 1).Value = Me.cboCategory.Value
      .Cells(lRow, 2).Value = Me.cboTitle.Value
      .Cells(lRow, 3).Value = Me.txtFirstName.Value
      .Cells(lRow, 4).Value = Me.txtLastName.Value
      .Cells(lRow, 5).Value = Me.txtHomeAddress.Value
      .Cells(lRow, 6).Value = Me.txtCity.Value
      .Cells(lRow, 7).Value = Me.txtPostalCode.Value
      .Cells(lRow, 8).Value = Me.txtDOB.Value
      .Cells(lRow, 9).Value = Me.txtContactNo.Value
      .Cells(lRow, 10).Value = Me.txtWorkNo.Value
      .Cells(lRow, 11).Value = Me.txtCellNo.Value
      .Cells(lRow, 12).Value = Me.txtemail.Value
      .Cells(lRow, 13).Value = Me.cboMeansContact.Value
      .Cells(lRow, 14).Value = Me.cboContactTime.Value
      .Cells(lRow, 15).Value = Me.cbodriverslicense.Value
      .Cells(lRow, 16).Value = Me.cboOwnCar.Value
      .Cells(lRow, 17).Value = Me.ListBoxHowDidYouHear.Value
       Dim strSelected As String
        strSelected = ""
        Dim i As Integer
        With Me.ListBoxHowDidYouHear
            For i = 0 To .ListCount - 1
                If .Selected(i) = True Then
                    If strSelected = "" Then
                        strSelected = .List(i)
                    Else
                        strSelected = strSelected & ", " & .List(i)
                    End If
                End If
            Next i
        End With

        .Cells(lRow, 17).Value = strSelected '17th column!
      .Cells(lRow, 18).Value = Me.txtOtherHearKalein.Value
      .Cells(lRow, 19).Value = Me.txtCurrentEmployment.Value
      .Cells(lRow, 20).Value = Me.txtNameEmployer.Value
      .Cells(lRow, 21).Value = Me.cboSelfEmployed.Value
      .Cells(lRow, 22).Value = Me.txtNameBusiness.Value
      .Cells(lRow, 23).Value = Me.txtNatureSelfEmployment.Value
      .Cells(lRow, 24).Value = Me.txtCurrentAffiliations.Value
      .Cells(lRow, 25).Value = Me.txtNotesAffiliations.Value
      .Cells(lRow, 26).Value = Me.ListBoxReasonVolunteer.Value
        strSelected = ""
        With Me.ListBoxReasonVolunteer
            For i = 0 To .ListCount - 1
                If .Selected(i) = True Then
                    If strSelected = "" Then
                        strSelected = .List(i)
                    Else
                        strSelected = strSelected & ", " & .List(i)
                    End If
                End If
            Next i
        End With

        .Cells(lRow, 26).Value = strSelected '26th column!
       .Cells(lRow, 27).Value = Me.txtOtherReasonVolunteer.Value
       .Cells(lRow, 28).Value = Me.cboSkills1.Value
       .Cells(lRow, 29).Value = Me.cboSkills2.Value
       .Cells(lRow, 30).Value = Me.txtOtherSkills.Value
       .Cells(lRow, 31).Value = Me.cboInquiringPosition.Value
       .Cells(lRow, 32).Value = Me.cboResume.Value
       .Cells(lRow, 33).Value = Me.txtNotesSkills.Value
       .Cells(lRow, 34).Value = Me.cboVolunteerAreas1.Value
       .Cells(lRow, 35).Value = Me.cboVolunteerAreas2.Value
       .Cells(lRow, 36).Value = Me.txtOtherVolunteerAreas.Value
       .Cells(lRow, 37).Value = Me.cboHospiceCareVolunteer.Value
       .Cells(lRow, 38).Value = Me.cboCompletedTraining.Value
       .Cells(lRow, 39).Value = Me.txtTrainingWhereWhen.Value
       .Cells(lRow, 40).Value = Me.cboInterestedTraining.Value
       .Cells(lRow, 41).Value = Me.cboAvailability.Value
       .Cells(lRow, 42).Value = Me.cboDaysAvailable1.Value
       .Cells(lRow, 43).Value = Me.cboDaysAvailable2.Value
       .Cells(lRow, 44).Value = Me.ListBoxTimeAvailable.Value
        strSelected = ""
        With Me.ListBoxTimeAvailable
            For i = 0 To .ListCount - 1
                If .Selected(i) = True Then
                    If strSelected = "" Then
                        strSelected = .List(i)
                    Else
                        strSelected = strSelected & ", " & .List(i)
                    End If
                End If
            Next i
        End With

        .Cells(lRow, 44).Value = strSelected '44th column!
       .Cells(lRow, 45).Value = Me.cboReceiveUpdates.Value
       .Cells(lRow, 46).Value = Me.cboReceiveDonorInfo.Value
       .Cells(lRow, 47).Value = Me.txtGeneralNotes.Value
       .Cells(lRow, 48).Value = Me.txtSpousePartner.Value
       .Cells(lRow, 49).Value = Me.txtEmergencyContact.Value
       .Cells(lRow, 50).Value = Me.txtEmergencyNumber.Value
       .Cells(lRow, 51).Value = Me.cboMinor.Value
       .Cells(lRow, 52).Value = Me.cboParentalConsent.Value
          
       End With

    'clear the data
    Me.cboCategory.Value = ""
    Me.cboTitle.Value = ""
    Me.txtFirstName.Value = ""
    Me.txtLastName.Value = ""
    Me.txtHomeAddress.Value = ""
    Me.txtCity.Value = ""
    Me.txtPostalCode.Value = ""
    Me.txtDOB.Value = ""
    Me.txtContactNo.Value = ""
    Me.txtWorkNo.Value = ""
    Me.txtCellNo.Value = ""
    Me.txtemail.Value = ""
    Me.cboMeansContact.Value = ""
    Me.cboContactTime.Value = ""
    Me.cbodriverslicense.Value = ""
    Me.cboOwnCar.Value = ""
    Me.ListBoxHowDidYouHear.Value = ""
    Me.txtOtherHearKalein.Value = ""
    Me.txtCurrentEmployment.Value = ""
    Me.txtNameEmployer.Value = ""
    Me.cboSelfEmployed.Value = ""
    Me.txtNameBusiness.Value = ""
    Me.txtNatureSelfEmployment.Value = ""
    Me.txtCurrentAffiliations.Value = ""
    Me.txtNotesAffiliations.Value = ""
    Me.ListBoxReasonVolunteer.Value = ""
    Me.txtOtherReasonVolunteer.Value = ""
    Me.cboSkills1.Value = ""
    Me.cboSkills2.Value = ""
    Me.txtOtherSkills.Value = ""
    Me.cboInquiringPosition.Value = ""
    Me.cboResume.Value = ""
    Me.txtNotesSkills.Value = ""
    Me.cboVolunteerAreas1.Value = ""
    Me.cboVolunteerAreas2.Value = ""
    Me.txtOtherVolunteerAreas.Value = ""
    Me.cboHospiceCareVolunteer.Value = ""
    Me.cboCompletedTraining.Value = ""
    Me.txtTrainingWhereWhen.Value = ""
    Me.cboInterestedTraining.Value = ""
    Me.cboAvailability.Value = ""
    Me.cboDaysAvailable1.Value = ""
    Me.cboDaysAvailable2.Value = ""
    Me.ListBoxTimeAvailable.Value = ""
    Me.cboReceiveUpdates.Value = ""
    Me.cboReceiveDonorInfo.Value = ""
    Me.txtGeneralNotes.Value = ""
    Me.txtSpousePartner.Value = ""
    Me.txtEmergencyContact.Value = ""
    Me.txtEmergencyNumber.Value = ""
    Me.cboMinor.Value = ""
    Me.cboParentalConsent.Value = ""
    Me.cboCategory.SetFocus

    End Sub

    Private Sub cmdClose_Click()
    Unload Me
    End Sub

    Thursday, August 2, 2012 8:41 PM

Answers

All replies

  • I believe ethat this is due to 1) one of your variables is not named or does not exist 2) one of your reference to the sheet does not exist.
    Friday, August 3, 2012 2:55 AM
  • Is the code stopping on a particular line when you get the error message? If so then let us know.

    Are the ListBoxes MultiSelect because if so, the following code example to clear the multi selections.

        Dim i As Long
       
        'Clearing a multi select list box
        With Me.ListBox1
            For i = 0 To .ListCount - 1
                .Selected(i) = False
            Next i
        End With
       
        'Clearing a single select listbox
        Me.ListBox2 = ""


    Regards, OssieMac

    Friday, August 3, 2012 5:18 AM
  • Hi,

    No, the code does not stop on a particular line.

    The code to clear the listbox selections worked, thank you!

    The invalid property value message is still coming up.

    Any ideas will be appreciated.

    thanks!

    d.

    Friday, August 3, 2012 3:42 PM
  • Hi again,

    I found the work around for the problem which is to set the combo boxes property of  "Match Required" property to false. However, I do want the Match Required capability so as to prevent unwanted entries.

    Can someone provide me the code to ensure that the user can only select from the combo box choices.

    Thank you!

    Friday, August 3, 2012 7:21 PM
  • Following clears the combo box selection

    Me.ComboBox1.ListIndex = -1


    Regards, OssieMac

    Friday, August 3, 2012 8:43 PM
  • I know that this is an old thread but I had the same problem and found the solution. For future readers trying to find the solution to this problem. The error is being generated because your value in the combobox is null and a blank string does not match any of the values in your list. To allow a null string add "" to your combobox list.

    me.combobox1.additem "", 0

    I like to have the first value be blank.

    Hope this helps

    • Proposed as answer by SoccerGodzilla Monday, December 14, 2015 11:37 PM
    • Unproposed as answer by SoccerGodzilla Monday, December 14, 2015 11:43 PM
    Monday, December 14, 2015 11:36 PM
  • Old thread, yes, but it's still an issue with this control, and having a null string entry in the ComboBox (even at the top) doesn't prevent it without further massaging of your code.

    I am working on a form with each ComboBox having the null string as the first item in the list, as I too like having (and needed) that option, and I was still getting the error.

    As it turns out, the key is NOT trying to set the VALUE of the Combo box to Null (cmbMyBox.Value = ""), as doing so works, but if you then set focus to the control and pull focus (press TAB, or using the mouse), you get the error message. I suspect it's due to "requiring match" and leaving the control with the ListIndex at -1 (no match). The fix is to instead set the ListIndex to the location where the Null string is (in my case, 0). Once I did this, the error message went away. 

    In my case, I wanted to set the value to match something read from a DB, so rather than having special cases for each value, I wrote a simple routine to do it for me:

    Private Function SetComboListIndex(cmbX As ComboBox, strVal) As Integer
        Dim intCtr As Integer
        
        For intCtr = 0 To cmbX.ListCount - 1
            If cmbX.list(intCtr, 0) = strVal Then
                SetComboListIndex = intCtr
                Exit Function
            End If
        Next
    End Function
    Then rather than set the value to my string, I call the subroutine as follows:

    Me.cbxZoneName.ListIndex = SetComboListIndex(Me.cbxZoneName, strValue)

    This works for me as I have the null string at ListIndex 0, and on no match, the routine sets the list index to 0, pointing to my null string.

    As SoccerGodzilla said: Hope this helps!

    Wednesday, February 21, 2018 2:18 PM
  • Can also be handled without the need for a zero length string in the list by using code like the following by setting the property "MatchRequired" to True when it is needed and to False when not needed.

    Private Sub ComboBox1_Enter()
        'Turn on MatchRequired when the user enters the ComboBox Control
        Me.ComboBox1.MatchRequired = True
    End Sub

    Private Sub ComboBox1_Change()
        If Me.ComboBox1.Value = "" Then
            'Match not required if zero lenght string
            Me.ComboBox1.MatchRequired = False
        Else
            'Match is required if other than zero length string
            Me.ComboBox1.MatchRequired = True
        End If
    End Sub

    Private Sub CommandButton1_Click()
        'If used to clear the controls in the Userform
        'Turn off MatchRequired if clearing the ComboBox
        Me.ComboBox1.MatchRequired = False
        Me.ComboBox1.ListIndex = -1
    End Sub


    Regards, OssieMac

    Thursday, February 22, 2018 1:06 AM
  • Hi there,

    Wanted to share my solution, even though it's an old thread.

    First off; the MatchEntry and MatchRequired are per default set to 'None' and on 'False'.

    In my case the ComboBox is a prerequisite for filling in the listboxes, like a category.

    So making sure a correct match is entered before I can head on, I block and reset all entries for the listbox when the input is a non-matching entry. I do this with the following code:

    Private Sub ComboBox1_Change()
    Dim r As Long
    Dim i As Long
    Dim ctrl As Control
    
    'if entry is matched then enable the next listboxes
    If Me.ComboBox1.MatchFound = True Then
        For i = 1 To 3
            For Each ctrl In Me.Controls
                Me.Controls("ListBox" & i).Enabled = True
            Next ctrl
        Next i
    Else
    'reset all entries for the list
        For i = 1 To 3
        With Me.Controls("Listbox" & i)
            For r = 0 To .ListCount - 1
                        If .Selected(r) Then
                            .Selected(r) = False
                        End If
            Next r
        r = 0
        End With
        Next
    
    'disable all listboxes
        For i = 1 To 3
            For Each ctrl In Me.Controls
                Me.Controls("ListBox" & i).Enabled = False
            Next ctrl
        Next i
    End If
    
    End sub

    When the entry is correct and you've finished filling in the other boxes you can let your button do it's work.

    In my case the button saves all data to the sheet and then resets the complete form.

    Clearing all listboxes and combobox with a commandbutton:

    Private Sub CommandButton1_Click() Dim r As Long Dim i As Long

    'do your coding and stuff here

    'next step is clearing everything again

    For i = 1 To 3 With Me.Controls("Listbox" & i) For r = 0 To .ListCount - 1 If .Selected(r) Then .Selected(r) = False End If Next r r = 0 End With Next Me.ComboBox1.ListIndex = -1 End Sub


    Cheers,

    Sven

    Tuesday, August 13, 2019 9:20 AM