This is pretty involved-but I have attached a workbook and the macros behind are supposed to do the following:
1) check to make sure each column that has a date (there are 3, birth date-start date-end date) are valid dates
2)check to make sure that the birth date is within a reasonable range of years
3) check to make sure that the age of the person is at least 18 (using the birth date and the start date)
4) check to make sure the start date is before the end date
5) check to make sure the start date and end date range is no more than 120 days
6) check to make sure the program type is either BASIC or SELF PLACED
These are all validated on the on save event--
AND
Last of all I have an on close and on open event that makes sure that the user enables the macros when they open the spreadsheet.
My problem is that some of the validation works, and some does not -- I have tested and tested and I can't figure out why--I am hoping that I am missing something very simple....
Any help is appreciated-----
Full Version: Help with macros
Hi Sue,
and which validation code is not working for you? Or is it that the validation works for some line and for some not?
Generally speaking, this approach might be hard to maintain and also the users are constantly bothered with the message until they correct all the cells before closing the workbook.
What I would consider is to use a dummy column(s) with a function that would return TRUE/FALSE and base conditional formatting on this formula(s). This would mark all the wrong cells in one step so that the user sees what is to be corrected.
Another possibility is to base Data=>Validation on these dummy formulas so that the user can't leave the cell until it's filled in correctly.
Martin
and which validation code is not working for you? Or is it that the validation works for some line and for some not?
Generally speaking, this approach might be hard to maintain and also the users are constantly bothered with the message until they correct all the cells before closing the workbook.
What I would consider is to use a dummy column(s) with a function that would return TRUE/FALSE and base conditional formatting on this formula(s). This would mark all the wrong cells in one step so that the user sees what is to be corrected.
Another possibility is to base Data=>Validation on these dummy formulas so that the user can't leave the cell until it's filled in correctly.
Martin
Attached is a newer version of the spreadsheet. I think your comments are valid but the errors on the sheets are few and it would take a lot of extra work and testing on my part to change it now. I would like to try to just get what I have working. The only two items that are not working are:
Function CheckStudentAge
I am sure it has to do with the construct of the If statement. It is only evaluating the student if he is less than 18 years of age. The other statement if he is older than 25 is not working.
Function ValidateStartDates
Same issue --
It is not evaluating if the difference between the startdate and enddate is greater than 120 days-
I would be very happy to be able to figure these two issues out and then I could move on!!
Function CheckStudentAge
I am sure it has to do with the construct of the If statement. It is only evaluating the student if he is less than 18 years of age. The other statement if he is older than 25 is not working.
Function ValidateStartDates
Same issue --
It is not evaluating if the difference between the startdate and enddate is greater than 120 days-
I would be very happy to be able to figure these two issues out and then I could move on!!
Hi Sue,
in mod_validate
Edited by: strive4peace2007 on Mon Jul 30 18:02:28 EDT 2007.
in mod_validate
CODE
For mRow = 2 To mLastRow
If DateDiff("YYYY", Cells(mRow, pCol1), Cells(mRow, pCol2)) < 18 Then
CheckStudentAge = False
Cells(mRow, pCol1).Select
pMsg = "Student must be at least 18 years of age; Student is" & " " & DateDiff("YYYY", Cells(mRow, pCol1), Cells(mRow, pCol2)) & " " & "years old"
Else
If DateDiff("YYYY", Cells(mRow, pCol1), Cells(mRow, pCol2)) > 25 Then
CheckStudentAge = False
Cells(mRow, pCol1).Select
pMsg = "Student must be no older than 25; Student is" & " " & DateDiff("YYYY", Cells(mRow, pCol1), Cells(mRow, pCol2)) & " " & "years old"
End If
End If
[color="red"]
If Len(pMsg) > 0 Then
Exit Function
End If
[/color]
Next mRow
If DateDiff("YYYY", Cells(mRow, pCol1), Cells(mRow, pCol2)) < 18 Then
CheckStudentAge = False
Cells(mRow, pCol1).Select
pMsg = "Student must be at least 18 years of age; Student is" & " " & DateDiff("YYYY", Cells(mRow, pCol1), Cells(mRow, pCol2)) & " " & "years old"
Else
If DateDiff("YYYY", Cells(mRow, pCol1), Cells(mRow, pCol2)) > 25 Then
CheckStudentAge = False
Cells(mRow, pCol1).Select
pMsg = "Student must be no older than 25; Student is" & " " & DateDiff("YYYY", Cells(mRow, pCol1), Cells(mRow, pCol2)) & " " & "years old"
End If
End If
[color="red"]
If Len(pMsg) > 0 Then
Exit Function
End If
[/color]
Next mRow
Edited by: strive4peace2007 on Mon Jul 30 18:02:28 EDT 2007.
Thanks for jumping in Crystal 
Martin
Martin
happy to help ... thanks, Martin
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.