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.
- 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.
- 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.
- I click on another record and recieve no error when loading, but I get the error 91 when selecting "Edit".
- 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.
- Some records load to the controls completely, while others in the list box only partially load the available data.
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