Error 380 - Couldn't set value/column property . Invalid property value or array Index

Rfriend

Board Regular
Joined
May 10, 2021
Messages
73
Office Version
  1. 2010
Platform
  1. Windows
I am receiving several errors in a contact database I built. One record gives one error, but the next gives a different one, and all are giving an error when I select the command button to edit the data loaded on double click from the listbox. This started only recently and I am not sure what changed it from working to errors.
  1. When I double click a record in the list box the record will load completely to the controls, but I recieve the error 380 - Could not set the Value property. Invalid property value.
  2. When I click OK, all the data is there, but clicking on the "Edit" command button will then throw error 91 - Object variable or With block variable not set.
  3. I click on another record and recieve no error when loading, but I get the error 91 when selecting "Edit".
  4. When I try to step through the code I see that some values are loaded on hover over the first part of the code, while others are not, and I receive the error Invalid array index.
  5. Some records load to the controls completely, while others in the list box only partially load the available data.
I have tried reloading code form a back up workbook I know to work, but the same thing happens in the current workbook. Any ideas or suggestions are appreciated.

VBA Code:
[B]Private Sub cmdContactEAP_Click()[/B]
    Dim EAPDataSH As Worksheet
    On Error GoTo errHandler:
    Set EAPDataSH = Sheet19
        EAPDataSH.Range("M5") = cboSelectEAP.Value
        EAPDataSH.Range("M6") = txtSearchEAP.Text
        Unprotect_All
            AdvFilterEAP
            lstbox1EAP.RowSource = EAPDataSH.Range("EAPOutdata").Address(external:=True)
        Protect_All
    Exit Sub
errHandler:
        Protect_All
            MsgBox "No match found for " & txtSearchEAP.Text
        On Error GoTo 0
    Exit Sub
End Sub

[B]Sub AdvFilterEAP()[/B]
    Sheet19.Range("EAPTable[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
    :=Sheet19.Range("M5:M6"), CopyToRange:=Sheet19.Range("Q6:Y6"), Unique _
    :=False
End Sub

[B]Private Sub lstbox1EAP_DblClick(ByVal Cancel As MSForms.ReturnBoolean)[/B]
    On Error GoTo lstbox1EAP_DblClick_Error
        Me.cmdAddNewContactEAP.Enabled = False
        Me.cmdEditEAP.Enabled = True
        Me.EAP1.Value = Me.lstbox1EAP.Value
        Me.EAP4.Value = Me.lstbox1EAP.Column(1)
        Me.EAP3.Value = Me.lstbox1EAP.Column(2)
        Me.EAP2.Value = Me.lstbox1EAP.Column(3)
        Me.EAP5.Value = Me.lstbox1EAP.Column(4)
        Me.EAP6.Value = Me.lstbox1EAP.Column(5)
        Me.EAP7.Value = Me.lstbox1EAP.Column(6)
        Me.EAP8.Value = Me.lstbox1EAP.Column(7)
        Me.EAP9.Value = Me.lstbox1EAP.Column(8)
        On Error GoTo 0
            EAPSortit3
    Exit Sub
lstbox1EAP_DblClick_Error:
    MsgBox "Error occured " & Err.Number & "(" & Err.Description & ") in proceedure lstbox1EAP_DblClick of Form Emergency_Contact_List"
End Sub

[B]Private Sub cmdEditEAP_Click()[/B]
    On Error GoTo cmdEditEAP_Click_Error
    If Me.EAP4 = "" Then
        Call MsgBox("Required information missing. Please complete data input.", vbInformation, "EditEAP Contact")
        Exit Sub
    End If
    Set findvalue = Sheet19.Range("K6:K1000").Find(What:=Me.EAP1, LookIn:=xlValues)
        findvalue.Offset(0, -9) = EAP2.Value
        findvalue.Offset(0, -8) = EAP3.Value
        'findvalue.Offset(0, -7) = EAP4.Value
        findvalue.Offset(0, -6) = EAP4.Value
        findvalue.Offset(0, -5) = EAP5.Value
        findvalue.Offset(0, -4) = EAP6.Value
        findvalue.Offset(0, -3) = EAP7.Value
        findvalue.Offset(0, -2) = EAP8.Value
        findvalue.Offset(0, -1) = EAP9.Value
        Call MsgBox("The contact has been updated", vbInformation, "Edit Contact")
            On Error GoTo 0
        'txtFull_Change
        EAPSortit3
    Exit Sub
cmdEditEAP_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdEditEAP_Click of Form Emergency_Contact_List"
End Sub
 

Attachments

  • Capture.PNG
    Capture.PNG
    5.3 KB · Views: 7
  • Capture2.PNG
    Capture2.PNG
    159.1 KB · Views: 8

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,214,925
Messages
6,122,298
Members
449,077
Latest member
Rkmenon

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