I am trying to add a Delete option on a userform. The record to delete is selected from a listbox and the userform is populated with the appropriate data.
I have looked at the code step-by-step and it appears that the error occurs at the findvalue.EntireRow.Delete line. I temporarily added a textbox to the form to insure that the value for "findvalue" was correct and it was indicating that the proper record was being located in the underlying table however I get a runtime error. Any help or suggestions would be appreciated.
Private Sub cmdDelete_Click()
'declare the variables
Dim findvalue As Range
Dim cDelete As VbMsgBoxResult
Dim cNum As Integer
Dim DataSH As Worksheet
Set DataSH = Sheet2
Dim x As Integer
'error statement
'On Error GoTo errHandler:
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
'check for values
If Bar1.Value = "" Or Bar2.Value = "" Then
MsgBox "There is not data to delete"
Exit Sub
End If
'give the user a chance to change their mind
cDelete = MsgBox("Are you sure that you want to delete this product? The data cannot be retrieved!", _
vbYesNo, "CAUTION!")
If cDelete = vbNo Then
MsgBox "Delete Cancelled", vbOKOnly, "Water & Bone"
Exit Sub
Else
'find the row
Set findvalue = DataSH.Range("BA:BA").Find(What:=Me.Bar2.Value, _
LookIn:=xlValues, LookAt:=xlWhole)
'*****************************************************************
'This is a test box that I added to check the value of findvalue *
'Everything works up to this point *
'Me.txtTest.Value = findvalue
'*****************************************************************
'delete the entire row
findvalue.EntireRow.Delete 'This is where I get the run time error 1004 delete method of range class failed
End If
On Error GoTo 0
Exit Sub
errHandler:
'Protect all sheets if error occurs
'Protect_All
'show error information in a messagebox
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " & _
Err.Number & vbCrLf & Err.Description & vbCrLf & "Please notify Chef Jamie"
End Sub
Bookmarks