Hello,
I am using a userform to add, edit and delete data from a table located in the workbook. (code below is to delete)
With the addition of a new row into the table, I give the row a unique number. When the user in the form double clicks in the Listbox on the line they want to remove.
the data is being returned to the boxes inside the form(same as used to add data).
The removal of the row relies on a unique number and uses that to search the table for the row to delete.
that nr is then located in me.Nr
when I try to delete the data sometimes it works 2 times then it throws an error:
-2147417848 Meth "delete of object listrow failed
Then in the worksheet "PlanningLCMSworksheet" where the table is located, the data from the row is deleted however the row is still there and I cannot select cells on the sheet anymore.
Also when I try to save the file is crashes.
when I then try to use the form to delete more it throws up another error on line:
Set datadelete = DataSH.Range("Plandata[Nr]"). _
Find(What:=Me.Nr.Value, LookIn:=xlValues, LookAt:=xlWhole)
With the error:
application-defined or object-defined error
I cannot find the reason why.
Any help would be appreciated
Cheers,
Yede
I am using a userform to add, edit and delete data from a table located in the workbook. (code below is to delete)
With the addition of a new row into the table, I give the row a unique number. When the user in the form double clicks in the Listbox on the line they want to remove.
the data is being returned to the boxes inside the form(same as used to add data).
The removal of the row relies on a unique number and uses that to search the table for the row to delete.
that nr is then located in me.Nr
when I try to delete the data sometimes it works 2 times then it throws an error:
-2147417848 Meth "delete of object listrow failed
Then in the worksheet "PlanningLCMSworksheet" where the table is located, the data from the row is deleted however the row is still there and I cannot select cells on the sheet anymore.
Also when I try to save the file is crashes.
when I then try to use the form to delete more it throws up another error on line:
Set datadelete = DataSH.Range("Plandata[Nr]"). _
Find(What:=Me.Nr.Value, LookIn:=xlValues, LookAt:=xlWhole)
With the error:
application-defined or object-defined error
I cannot find the reason why.
Any help would be appreciated
Cheers,
Yede
VBA Code:
Private Sub Deletebtn_Click()
'declare the variables
Dim datadelete As Range
Dim cDelete As VbMsgBoxResult
Dim DataSH As Worksheet
Dim tabelnaam As String
Dim tabelobject As ListObject
tabelnaam = "Plandata"
Set DataSH = Sheets("PlanningLCMSworksheet")
Set tabelobject = DataSH.ListObjects(tabelnaam)
'error statement
On Error GoTo errHandler:
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
'check for values
If Nr.Value = "" Or dagweek.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 the data?", _
vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then
'find the row
Set datadelete = DataSH.Range("Plandata[Nr]"). _
Find(What:=Me.Nr.Value, LookIn:=xlValues, LookAt:=xlWhole)
'Delete value
tabelobject.ListRows(datadelete).delete
End If
Private Sub Deletebtn_Click()
'declare the variables
Dim datadelete As Range
Dim cDelete As VbMsgBoxResult
Dim DataSH As Worksheet
Dim tabelnaam As String
Dim tabelobject As ListObject
tabelnaam = "Plandata"
Set DataSH = Sheets("PlanningLCMSworksheet")
Set tabelobject = DataSH.ListObjects(tabelnaam)
'error statement
'On Error GoTo errHandler:
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
'check for values
If Nr.Value = "" Or dagweek.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 the data?", _
vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then
'find the row
Set datadelete = DataSH.Range("Plandata[Nr]"). _
Find(What:=Me.Nr.Value, LookIn:=xlValues, LookAt:=xlWhole)
'Delete value
tabelobject.ListRows(datadelete).delete
End If
Application.ScreenUpdating = True
Unload Me
Toevoegen.Show
Exit Sub
errHandler:
'show error information in a messagebox
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " & _
Err.Number & vbCrLf & Err.Description & vbCrLf & "Please notify the administrator"
End Sub
Unload Me
Toevoegen.Show
Exit Sub
errHandler:
'show error information in a messagebox
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " & _
Err.Number & vbCrLf & Err.Description & vbCrLf & "Please notify the administrator"
End Sub