View Single Post
  #7 (permalink)  
Old November 15th, 2005, 06:00 PM
tonyrosen tonyrosen is offline
Authorized User
 
Join Date: Nov 2005
Location: Houston, TX, .
Posts: 16
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via Yahoo to tonyrosen
Default

Okay, here's my SUB

=============

Sub ValidateStopDate()
    MyRow = ActiveCell.Row
    'This is the Stop Date Column Number
    MyColumn = ActiveCell.Column
    'This is the Start Date Column Number
    MyColumnPrev = MyColumn - 1

    If MyColumn > 26 Then
        MyColumnLetter = Chr(Int((MyColumn - 1) / 26) + 64) & Chr(((MyColumn - 1) Mod 26) + 65)
    Else
        MyColumnLetter = Chr(MyColumn + 64)
    End If
    'MyColumnLetter = Mid(ActiveCell.Address, 2, (InStr(2, ActiveCell.Address, "$")) - 2)


    MyColumnNext = MyColumn + 1
    If MyColumnNext > 26 Then
        MyColumnLetterNext = Chr(Int((MyColumnNext - 1) / 26) + 64) & Chr(((MyColumnNext - 1) Mod 26) + 65)
    Else
        MyColumnLetterNext = Chr(MyColumnNext + 64)
    End If

    MyRange = Range(MyColumnLetter & MyRow)
    MyNextRange = Range(MyColumnLetterNext & MyRow)

    Range("H3") = MyNextRange
    'Range("G3") = MyColumnLetterNew

    'MyNewRange = Column F
    'MyRange = Column E
    'If Year(F) + 1 > Year(E)
    StopMonth = Month(MyRange)
    StopDay = Day(MyRange)
    StopYear = Year(MyRange)
    YearOne = MyNextRange

    If StopYear < YearOne Then
        'StopDate is no good
        MsgBox ("Your Stop Date cannot occur prior to 12/31/" & YearOne & ".")
    Else
        If StopYear = YearOne Then
            If StopMonth < 12 And StopDay < 31 Then
                'StopDate is no good
                MsgBox ("Your Stop Date cannot be sooner than 12/31/" & YearOne & ".")
            Else
                'StopDate is after 12/31/YearOne
                Range("H3") = "OKAY"
            End If
        End If
    End If
End Sub

=============

And, my Change ...

=============

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("F2")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Call ValidateStopDate
    End If
End Sub

===================

It won't run the macro call ... help(?)

Reply With Quote