Validation Data

grache

New Member
Joined
Apr 22, 2002
Messages
3
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top