Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Formula (Excel 2K)

    Good Morning,

    What I'm trying to accomplish, w/o putting the formula in a cell, is provide the persons age based on their birthdate.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim wshSource As Worksheet
    Dim birRange As Range, ageRange As Range
    Dim ageInt As Integer

    Set wshSource = Worksheets("Identify")
    Set birRange = wshSource.Range("E:E")
    Set ageRange = wshSource.Range("F:F")
    ageVar = DateDiff(birRange, Now(), "y")

    If Not Intersect(Target, birRange) Is Nothing Then
    If Intersect(Target, birRange).Cells(1) <> "" Then
    ageRange = ageVar
    End If
    End If

    But I get an error msg "type mis match"...any ideas what I'm doing wrong this time?

    Thanks in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Formula (Excel 2K)

    If you get an error message, it is useful to mention which line is highlighted, so that wer odn't have to guess.

    The immediate problem is that the arguments to DateDiff are in the wrong order: the syntax is DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]]), for example DateDiff("d", Now, TheDate) (this is from the online help)

    But repairing that wouldn't help much; the logic for your code is unclear. You cannot calculate an age for an entire column in one variable. Formulas would be much easier here. Why do it the hard way?

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formula (Excel 2K)

    Hans,

    So sorry for omitting where the error msg is coming from.

    It's coming from this line: ageVar = DateDiff(birRange, Now(), "y")

    I didn't think of it in terms you are stating...rather as the user inputs a birthdate then the age would be calculated in the column respective to the entry.

    The reason, I suppose, as you say, for doing it the hard way, is because I can't protect the column (therefore I can't protect the formula). I have other codes running that won't work if the worksheet is protected.

    I hope that answered all your questions.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Formula (Excel 2K)

    You don't need the SelectionChange event, but the Change event, and you need quite different code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim intAge As Integer

    If Not Intersect(Target, Range("E:E")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("E:E"))
    If IsDate(oCell) Then
    intAge = Year(Date) - Year(oCell)
    If Month(Date) < Month(oCell) Or _
    (Month(Date) = Month(oCell) And Day(Date) < Day(oCell)) Then
    intAge = intAge - 1
    End If
    oCell.Offset(0, 1) = intAge
    Else
    oCell.Offset(0, 1) = ""
    End If
    Next oCell
    End If

    Application.EnableEvents = True
    Set oCell = Nothing
    End Sub

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formula (Excel 2K)

    As always Hans, you are greatly appreciated. It seems as someone would enter data for the birthdate the formula would work w/o any problem but as the user moved forward and make a mistake they would inevidabley delete the formula as well as the AGE and of course complain the spreadsheet wasn't working. I had to find a way to prevent this from happening.

    As I usually do, fumble my way around, tried to figured it out for myself....but it's nothing like Access...and I'm forever lost....so when I say ... "you are greatly appreciated"...it's genuinly meant.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formula (Excel 2K)

    Several comments:

    1- You are using the wrong event to accomplish what you are trying to do. In the Worksheet_SelectionChange event routine, the Target parameter is the cell range of the cell that the selection is changing to. Therefore, you can not use it to determine if the cell that was changed contained a birth date. You want to use the Worksheet_Change event routine. In that routine, the Target parameter points to the cell that was just changed.

    2- Your routine will probably not do what you want if the user clears the birth date cell. In that case, it will leave the previously calculated age in the age cell.

    3- Your routine will not work correctly if the user pastes ages into more than one cell. It will only calculate the age for the selected cell in the paste range. This will either leave the age cells for the other cells in the paste range blank or containing an incorrect previously calculated range.

    4- There should be no reason that you can not use a formula on the worksheet to calculate the age, and then protect the worksheet with the cells containing the formula being protected. This is exactly what worksheet protection is for.

    If you must use an event routine to do this, then you need something like this:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("E:E"))
    If oCell.Value = "" Then
    Range("F1").Offset(oCell.Row - 1).Clear
    Else
    Range("F1").Offset(oCell.Row - 1).Value = DateDiff("yyyy", oCell.Value, Now())
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub
    </pre>

    Legare Coleman

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formula (Excel 2K)

    Thank you Legare,

    As I mentioned previously, it wasn't the formula that wasn't working (unless of course it was deleted in error by the user) it is other codes that won't work if the worksheet is protected. However all is well now. Thanks for your suggestion.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formula (Excel 2K)

    I'm not sure what "it is other codes" means. If that is VBA code, it should be possible to make it work if the worksheet is protected. The code can unprotect the worksheet, do whatever it needs to do, then re-protect the worksheet. If "other codes" means something else, could you explain?
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •