mikekhel1987
11-11-2015, 02:38 AM
I have created a userform ( Delete userform to be specific ) on my employee database. If there is a resign employee we want to delete it in our database using the form. I just want to ask if every time that there is a resign employee I can copy first the whole data of the employee to another worksheet for future reference before deleting it the database? below is the code that i used in the delete userform button.
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 Emp1.Value = "" Or Emp2.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 employee?", _
vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then
'find the row
Set findvalue = DataSH.Range("B:B").Find(What:=Me.Emp1.Value, _
LookIn:=xlValues, LookAt:=xlWhole)
'delete the entire row
findvalue.EntireRow.Delete
End If
'clear the controls
cNum = 14
For x = 1 To cNum
Me.Controls("Emp" & x).Value = ""
Next
'unprotect all sheets for the advanced filter
'Unprotect_All
'filter the data
DataSH.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Data!$S$8:$S$9"), CopyToRange:=Range("Data!$U$8:$AH$8"), _
Unique:=False
'if no data exists then clear the rowsource
If DataSH.Range("U9").Value = "" Then
lstEmployee.RowSource = ""
Else
'add the filtered data to the rowsource
lstEmployee.RowSource = DataSH.Range("outdata").Address(external:=True)
End If
'sort the data by "Surname"
DataSH.Select
With DataSH
.Range("B9:O10000").Sort Key1:=Range("C9"), Order1:=xlAscending, Header:=xlGuess
End With
'Protect all sheets
'Protect_All
'return to sheet
Sheet1.Select
'error block
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 the administrator"
End Sub
I hope you can help me on this. Thank you in advance. any help will be very much appreciated.
:help:help: pray2:: pray2:
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 Emp1.Value = "" Or Emp2.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 employee?", _
vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then
'find the row
Set findvalue = DataSH.Range("B:B").Find(What:=Me.Emp1.Value, _
LookIn:=xlValues, LookAt:=xlWhole)
'delete the entire row
findvalue.EntireRow.Delete
End If
'clear the controls
cNum = 14
For x = 1 To cNum
Me.Controls("Emp" & x).Value = ""
Next
'unprotect all sheets for the advanced filter
'Unprotect_All
'filter the data
DataSH.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Data!$S$8:$S$9"), CopyToRange:=Range("Data!$U$8:$AH$8"), _
Unique:=False
'if no data exists then clear the rowsource
If DataSH.Range("U9").Value = "" Then
lstEmployee.RowSource = ""
Else
'add the filtered data to the rowsource
lstEmployee.RowSource = DataSH.Range("outdata").Address(external:=True)
End If
'sort the data by "Surname"
DataSH.Select
With DataSH
.Range("B9:O10000").Sort Key1:=Range("C9"), Order1:=xlAscending, Header:=xlGuess
End With
'Protect all sheets
'Protect_All
'return to sheet
Sheet1.Select
'error block
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 the administrator"
End Sub
I hope you can help me on this. Thank you in advance. any help will be very much appreciated.
:help:help: pray2:: pray2: