Exercise 51 Data validation If a user accidently enters the wrong data or data is missing we want to be able to set in place some rules of checking that information, this is known as Navigate to the VBA Editor window we are going to set up validation. the following validations. sets 2 values in the drop down list as soon as you start typing in to employee name. “select from list” Private Sub txt_employee_name_Change() txt_department.Value = "select from List" txt_job.Value = "select from List" End Sub Checks to see if there is text in Employee Name if not a message box is displayed , when you click ok on the message box, focus is placed back to the cell to re-enter the data Private Sub txt_employee_name_Exit(ByVal Cancel As MSForms.ReturnBoolean) If IsNumeric(txt_employee_name) = True Then MsgBox "text entry only" Cancel = True End If End Sub Checks to see if text in “Department” equals “select from list” if yes a message box is displayed “select from list” , when you click ok on the message box, focus is placed back to the cell to re-enter the data Private Sub txt_department_Exit(ByVal Cancel As MSForms.ReturnBoolean) If txt_department.Value = "select from List" Then MsgBox "Please select item from list" Cancel = True End If End Sub
Exercise 51 Data validation If a user accidently enters the wrong data or data is missing we want to be able to set in place some rules of checking that information, this is known as validation. Navigate to the VBA Editor window we are going to set up the following validations. sets 2 values in the drop down list as soon as you start typing in to employee name. “select from list” Private Sub txt_employee_name_Change() txt_department.Value = "select from List" txt_job.Value = "select from List" End Sub Checks to see if there is text in Employee Name if not a message box is displayed , when you click ok on the message box, focus is placed back to the cell to re-enter the data Private Sub txt_employee_name_Exit(ByVal Cancel As MSForms.ReturnBoolean) If IsNumeric(txt_employee_name) = True Then MsgBox "text entry only" Cancel = True End If End Sub Checks to see if text in “Department” equals “select from list” if yes a message box is displayed “select from list” , when you click ok on the message box, focus is placed back to the cell to re-enter the data Private Sub txt_department_Exit(ByVal Cancel As MSForms.ReturnBoolean) If txt_department.Value = "select from List" Then MsgBox "Please select item from list" Cancel = True End If End Sub
Excel%20VBA%20Course%20Exercises%2014-3-13.docx