My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Help with macros
|
|
 Jul 29 2007, 07:00 PM
Post
#1
|
|
|
UtterAccess Addict Posts: 256 From: NH, USA  |
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-----
Attached File(s)
|
 |
 
|
|
Jul 30 2007, 01:48 AM
Post
#2
|
|
|
Retired Moderator Posts: 10,959 From: Prague,CZ / Kiev,UA |
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 |
|
|
|
|
Jul 30 2007, 03:22 PM
Post
#3
|
|
|
UtterAccess Addict Posts: 256 From: NH, USA |
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!!
Attached File(s)
|
|
|
|
|
Jul 30 2007, 05:01 PM
Post
#4
|
|
|
UtterAccess VIP Posts: 20,211 From: Colorado |
Hi Sue,
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 Edited by: strive4peace2007 on Mon Jul 30 18:02:28 EDT 2007. |
|
|
|
|
Jul 31 2007, 07:29 AM
Post
#5
|
|
|
Retired Moderator Posts: 10,959 From: Prague,CZ / Kiev,UA |
Thanks for jumping in Crystal (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)
Martin |
|
|
|
|
Jul 31 2007, 03:48 PM
Post
#6
|
|
|
UtterAccess VIP Posts: 20,211 From: Colorado |
happy to help ... thanks, Martin
|
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 25th May 2013 - 03:12 AM |