Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Validation Data

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a worksheet that contains one column where an amount is entered if an offer is made ($). The next column contains the date the offer was made.
    I want to set up validation rules in the date column to insist that whenever an amount is placed in the offer column, a date must be placed in the date column. Only some rows contain offers.
    Thanks.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Grache...
    What are your columns?
    Offer = ?
    Date = ?
    Thanks,
    Tom

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom
    column J contains the settlement amount offered (ie $10,000). Column K contains the date the offer was made (ie 19/04/02)
    Thanks

    On 2002-04-24 00:08, TsTom wrote:
    Hi Grache...
    What are your columns?
    Offer = ?
    Date = ?
    Thanks,
    Tom

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Grache.
    This code will force the issue.
    If any change is detected in the
    offer column, the code will check
    for a valid numeric amount. If not
    valid, a message will appear warning
    the user to enter a valid numeric
    amount and focus will be reurned to
    that cell. If the number is valid, then
    a message will warn the user to enter a
    date in the date column. No exit from
    the cell will be allowed until a valid date
    is entered.
    To use this procedure, simply right-click
    on your sheet tab, choose "View Code", and
    then paste this in the window which now appears.
    Close this window and try and enter some text in
    the Offer column.

    Tom

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 10 Then
    If Not IsNumeric(Target) Then
    MsgBox "Numeric amount expected. Please Re-Enter"
    Target.Select
    Exit Sub
    End If
    Cells(Target.Row, Target.Column + 1).Select
    MsgBox "Please enter a date in the selected cell"
    Application.EnableEvents = False
    Do Until IsDate(Cells(Target.Row, Target.Column + 1).Value)
    DoEvents
    If ActiveCell.Address <> Cells(Target.Row, Target.Column + 1).Address And _
    Not IsDate(Cells(Target.Row, Target.Column + 1).Value) Then
    MsgBox "A date must be entered to continue"
    Cells(Target.Row, Target.Column + 1).Select
    End If
    Loop
    Application.EnableEvents = True
    End If
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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