locked
Vba Excel Could not set the value property, invalid property value RRS feed

  • Question

  • Hi

    I have a userform with listbox and textbox. My textbox use to find the value in worksheet and fill to my listbox.

    I have a total of 13 volumns. So trying this process, i got an error saying that "Could not set the value property, invalid property value " and point the cursor with yellow color to this codes .List(.ListCount - 1, 10) = i.Offset(0, 10).Value

    when removing this codes, it is working properly. May i ask your help guys on how to fixed this or any idea. thanks.

    here is my code:

    Private Sub CmbSearch_Click()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim SearchRange As Range
        Dim FindWhat As Variant
        Dim FoundCells As Range
        Dim FoundCell As Range
        
        Set wb = ThisWorkbook
        Set ws = wb.Sheets("TransactionDB")
        
        If Me.TextBox1.Value <> "" Then
            FindWhat = TextBox1.Value
            Set SearchRange = ws.Range(ws.Cells(1, 1), ws.Cells(Rows.Count, 1).End(xlUp))
     
            Set FoundCells = FindAll(SearchRange:=SearchRange, _
                                FindWhat:=FindWhat, _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByColumns, _
                                MatchCase:=False, _
                                BeginsWith:=vbNullString, _
                                EndsWith:=vbNullString, _
                                BeginEndCompare:=vbTextCompare)
                                
                                
            If FoundCells Is Nothing Then
            'Debug.Print "Value Not Found"
      
            Else
                For Each i In FoundCells
                    With Me.ListBox1
                        .AddItem i.Value
                        .List(.ListCount - 1, 1) = i.Offset(0, 1).Value
                        .List(.ListCount - 1, 2) = i.Offset(0, 2).Value
                        .List(.ListCount - 1, 3) = i.Offset(0, 3).Value
                        .List(.ListCount - 1, 4) = i.Offset(0, 4).Value
                        .List(.ListCount - 1, 5) = i.Offset(0, 5).Value
                        .List(.ListCount - 1, 6) = i.Offset(0, 6).Value
                        .List(.ListCount - 1, 7) = i.Offset(0, 7).Value
                        .List(.ListCount - 1, 8) = i.Offset(0, 8).Value
                        .List(.ListCount - 1, 9) = i.Offset(0, 9).Value
                        .List(.ListCount - 1, 10) = i.Offset(0, 10).Value
                    End With
                Next i
            End If
            MsgBox "Records Save!", vbInformation
        End If
    End Sub

    Monday, March 3, 2014 8:26 AM

Answers

  • Since this is an Unbound ListBox, there is a cap on the columns, only 10 columns allowed unfortunately. For more info : http://office.microsoft.com/en-us/excel-help/HV080556371.aspx

    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    • Proposed as answer by Marvin_Guo Tuesday, March 4, 2014 5:53 AM
    • Marked as answer by Marvin_Guo Tuesday, March 11, 2014 1:22 AM
    Monday, March 3, 2014 11:11 AM