Hi everyone,
I have the following vab code used for Adding, Editing and Removing employees data thru one user form,
It is working just fine, but when adding a new employee's data, it adds the data to the database, but refuses to hide the user form after that, while it hides it when editing or removing an employees data.
Private Sub CommandButton1_Click()
If Not OptionButton1 And Not OptionButton2 And Not OptionButton3 Then
MsgBox "You have to select one three options on the top of the form!", vbExclamation, "No Selection"
Exit Sub
End If
If OptionButton1 Or OptionButton2 Then
If ComboBox1 = "" Then
MsgBox "The Employee's Name was not entered!", vbExclamation, "Missing Data"
ComboBox1.SetFocus
Exit Sub
ElseIf ComboBox2 = "" Then
MsgBox "The Employee's Badge Numbder was not entered!", vbExclamation, "Missing Data"
ComboBox2.SetFocus
Exit Sub
ElseIf Not ComboBox2 = "" And Not IsNumeric(ComboBox2) Then
MsgBox "The entry in this field must be a number!", vbExclamation, "Wrong Entry"
ComboBox2.SetFocus
Exit Sub
ElseIf Not ComboBox2 = "" And Not Len(ComboBox2) = 5 Then
MsgBox "The employee's Badge Number must consist of five digits!", vbExclamation, "Wrong Entry"
ComboBox2.SetFocus
Exit Sub
ElseIf OptionButton1 And WorksheetFunction.CountIf(BadgRng, ComboBox2) > 0 Then
MsgBox "The employee's Badge Number (" & ComboBox2 & ") is already available in the database!", vbExclamation, "Wrong Entry"
ComboBox2.SetFocus
Exit Sub
ElseIf ComboBox3 = "" Then
MsgBox "The Employee's Job Title was not selected!", vbExclamation, "Missing Data"
ComboBox3.SetFocus
Exit Sub
ElseIf ComboBox4 = "" Then
MsgBox "The Employee's Grade Code was not selected!", vbExclamation, "Missing Data"
ComboBox4.SetFocus
Exit Sub
ElseIf ComboBox5 = "" Then
MsgBox "The Employee's Work Location was not selected!", vbExclamation, "Missing Data"
ComboBox5.SetFocus
Exit Sub
Else
If MsgBox("Are you sure you want to " & Frame1.Caption & " data of the employee (" & ComboBox1 & ") ?", vbYesNo + vbQuestion, "Confirmation") = vbYes Then
If OptionButton1 Then
With Sheet1.[C103].End(xlUp)
.Offset(1, 0) = WorksheetFunction.Proper(ComboBox1)
.Offset(1, 1) = ComboBox2
.Offset(1, 2) = ComboBox3
.Offset(1, 3) = ComboBox4
.Offset(1, 4) = ComboBox5
End With
ElseIf OptionButton2 Then
With BadgRng.Find(ComboBox2)
.Offset(0, -1) = WorksheetFunction.Proper(ComboBox1)
.Offset(0, 1) = ComboBox3
.Offset(0, 2) = ComboBox4
.Offset(0, 3) = ComboBox5
End With
Unload Me
MsgBox "All data of the employee (" & ComboBox1 & ") were succesfully " & Frame1.Caption & "ed)!", vbInformation, "Result"
End If
End If
End If
ElseIf OptionButton3 Then
If MsgBox("Are you sure you want to remove all data of the employee (" & ComboBox1 & ")?", vbYesNo + vbQuestion, "Confirmation") = vbYes Then
Sheet1.Range("C" & AC & ":G" & AC) = ""
Unload Me
MsgBox "All Data of the employee (" & ComboBox1 & ") were succesfully removed!", vbInformation, "Result"
End If
End If
End Sub
I hope that I can find a solution for this problem in this great forum,
Best Regards,
Bookmarks