Could not set the RowSource property. Invalid property value. Error 380

[acon08]

Hi,

Can you please help me why Im getting and Error 380 on my code please. In the red text thats where Im getting the error.

Thank you so so much in advance.

Private Sub cmdAdd_Click()
        Dim sh As Worksheet
        Set sh = ThisWorkbook.Sheets("Worksheet")
        Dim le As Long
        lr = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row

        '''''''''''''''''Validation'''''''''''''''''''''''''''''''
       
        If Me.txtName.Value = "" Then
        MsgBox "Please Enter the Customer Name", vbCritical
        Exit Sub
        End If
       
        If IsNumeric(Me.txtName.Value) = True Then
        MsgBox "Please Enter the correct name", vbCritical
        Exit Sub
        End If
       
        If Me.txtBoxNo.Value = "" Then
        MsgBox "Please Enter the Box Number!", vbCritical
        Exit Sub
        End If
       
       
        ''''''''''''''''Adding Data''''''''''''''''''''''''''''''
       
        With sh
            .Cells(lr + 1, "A").Value = Me.txtBoxNo.Value
            .Cells(lr + 1, "B").Value = Me.cmbBoxSize.Value
            .Cells(lr + 1, "C").Value = Me.txtName.Value
            .Cells(lr + 1, "D").Value = Me.txtAddress.Value
            .Cells(lr + 1, "E").Value = Me.txtCity.Value
            .Cells(lr + 1, "F").Value = Me.txtPhoneNo.Value
            .Cells(lr + 1, "G").Value = Me.txtReceiverName.Value
            .Cells(lr + 1, "H").Value = Me.txtReceiverAddress.Value
            .Cells(lr + 1, "I").Value = Me.txtReceiverPhoneNo.Value
            .Cells(lr + 1, "J").Value = Me.cmbLocation.Value
        End With
       
        ''''''''''''''''Clearning the Entry Boxes''''''''''''''''''
       
        Me.txtBoxNo.Value = ""
        Me.cmbBoxSize.Value = ""
        Me.txtName.Value = ""
        Me.txtAddress.Value = ""
        Me.txtCity.Value = ""
        Me.txtPhoneNo.Value = ""
        Me.txtReceiverName.Value = ""
        Me.txtReceiverAddress.Value = ""
        Me.txtReceiverPhoneNo.Value = ""
        Me.cmbLocation.Value = ""
       
       
        Call Refresh_data
       
        MsgBox "Data has been added in the Worksheet", vbInformation
        txtBoxNo.SetFocus
       
End Sub


Sub Refresh_data()
        Dim sh As Worksheet
        Set sh = ThisWorkbook.Sheets("Worksheet")
        Dim le As Long
        lr = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
       
        If lr = 6 Then lr = 7
       
        With Me.ListBox
            .ColumnCount = 10
            .ColumnHeads = True
            .ColumnWidths = "40,130,90,130,100,80,80,80,90, 130"
            .RowSource = "Worksheet!A7:J" & lr
                       
        End With

            
End Sub

Private Sub cmdDelete_Click()
        Dim X As Long
        Dim Y As Long
        X = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
        For Y = 7 To X
       
        If Sheets("Worksheet").Cells(Y, 1).Value = txtSearch.Text Then
        Rows(Y).Delete
       
        End If
        Next Y
       
        ''''''''''''''''Clearning the Entry Boxes''''''''''''''''''
       
        Me.txtBoxNo.Value = ""
        Me.cmbBoxSize.Value = ""
        Me.txtName.Value = ""
        Me.txtAddress.Value = ""
        Me.txtCity.Value = ""
        Me.txtPhoneNo.Value = ""
        Me.txtReceiverName.Value = ""
        Me.txtReceiverAddress.Value = ""
        Me.txtReceiverPhoneNo.Value = ""
        Me.cmbLocation.Value = ""
        Me.txtSearch.Value = ""
       
        MsgBox "Data has been deleted from the Worksheet", vbInformation
        txtBoxNo.SetFocus

End Sub

Private Sub cmdExit_Click()
        If MsgBox("Do you want to exit this form?", vbQuestion + vbYesNo, "Confirmation") = vbYes Then
        Unload Me
        End If
End Sub

Private Sub cmdReset_Click()
        Unload Me
        UserForm1.Show
       
End Sub

Private Sub cmdSearch_Click()
        Dim X As Long
        Dim Y As Long
        X = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
        For Y = 7 To X
       
        If txtSearch.Text = Sheets("Worksheet").Cells(Y, 1).Value Then
        txtBoxNo = Sheets("Worksheet").Cells(Y, 1).Value
        cmbBoxSize = Sheets("Worksheet").Cells(Y, 2).Value
        txtName = Sheets("Worksheet").Cells(Y, 3).Value
        txtAddress = Sheets("Worksheet").Cells(Y, 4).Value
        txtCity = Sheets("Worksheet").Cells(Y, 5).Value
        txtPhoneNo = Sheets("Worksheet").Cells(Y, 6).Value
        txtReceiverName = Sheets("Worksheet").Cells(Y, 7).Value
        txtReceiverAddress = Sheets("Worksheet").Cells(Y, 8).Value
        txtReceiverPhoneNo = Sheets("Worksheet").Cells(Y, 9).Value
        cmbLocation = Sheets("Worksheet").Cells(Y, 10).Value
        Me.txtSearch.Value = ""
       
       
        End If
        Next Y

       
End Sub

Private Sub cmdUpdate_Click()
        Dim X As Long
        Dim Y As Long
        X = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
        For Y = 7 To X
       
        If Sheets("Worksheet").Cells(Y, 1).Value = txtSearch.Text Then
        Sheets("Worksheet").Cells(Y, 1).Value = txtBoxNo
        Sheets("Worksheet").Cells(Y, 2).Value = cmbBoxSize
        Sheets("Worksheet").Cells(Y, 3).Value = txtName
        Sheets("Worksheet").Cells(Y, 4).Value = txtAddress
        Sheets("Worksheet").Cells(Y, 5).Value = txtCity
        Sheets("Worksheet").Cells(Y, 6).Value = txtPhoneNo
        Sheets("Worksheet").Cells(Y, 7).Value = txtReceiverName
        Sheets("Worksheet").Cells(Y, 8).Value = txtReceiverAddress
        Sheets("Worksheet").Cells(Y, 9).Value = txtReceiverPhoneNo
        Sheets("Worksheet").Cells(Y, 10).Value = cmbLocation
       
        End If
        Next Y
       
        ''''''''''''''''Clearning the Entry Boxes''''''''''''''''''
       
        Me.txtBoxNo.Value = ""
        Me.cmbBoxSize.Value = ""
        Me.txtName.Value = ""
        Me.txtAddress.Value = ""
        Me.txtCity.Value = ""
        Me.txtPhoneNo.Value = ""
        Me.txtReceiverName.Value = ""
        Me.txtReceiverAddress.Value = ""
        Me.txtReceiverPhoneNo.Value = ""
        Me.cmbLocation.Value = ""
        Me.txtSearch.Value = ""
       
        MsgBox "Data has been updated in the Worksheet", vbInformation
        txtBoxNo.SetFocus
End Sub


Private Sub Image2_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As MSForms.fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

End Sub


Private Sub ListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        txtSearch.Text = ListBox.Column(0)
        If txtSearch.Text = ListBox.Column(0) Then
        txtBoxNo.Text = Me.ListBox.Column(0)
        cmbBoxSize.Text = Me.ListBox.Column(1)
        txtName.Text = Me.ListBox.Column(2)
        txtAddress.Text = Me.ListBox.Column(3)
        txtCity.Text = Me.ListBox.Column(4)
        txtPhoneNo.Text = Me.ListBox.Column(5)
        txtReceiverName.Text = Me.ListBox.Column(6)
        txtReceiverAddress.Text = Me.ListBox.Column(7)
        txtReceiverPhoneNo.Text = Me.ListBox.Column(8)
        cmbLocation.Text = Me.ListBox.Column(9)
       
        End If
       
   
End Sub

Private Sub UserForm_Activate()
        cmbBoxSize.List = Array("JB", "SB", "TV", "LC", "LC/TV")
        cmbLocation.List = Array("Cebu", "Bohol", "Negross Oriental", "Negros Occidental")
        Call Refresh_data
End Sub

 

[RoryA]

You have to use the List property to assign an array of the values, but you cannot use column headers with that.

 

[acon08]

You have to use the List property to assign an array of the values, but you cannot use column headers with that.

is it possible for you to send me a sample code to my error please, im not quite expert into vba. Thank you so much in advance.

 

[RoryA]

You'd use:

.List = Worksheets("Worksheet").Range("A7:J" & lr).Value

 

[acon08]

You'd use:

.List = Worksheets("Worksheet").Range("A7:J" & lr).Value

I just need to paste this code right? Thanks for your prompt reply RoryA

 

[RoryA]

That goes in place of the .Rowsource line, and you may as well remove the .ColumnHeads line as it won't work.

 

[acon08]

That goes in place of the .Rowsource line, and you may as well remove the .ColumnHeads line as it won't work.

Thanks RoryA you're the best Is there anyway to get the ColumnHeads work?

 

[RoryA]

No, not really. You have to fudge it in some way - usually either by putting them as the first row of data and not allowing that to be selected, or by adding labels above the listbox.