Keith
There can be only one Worksheet_Change macro per sheet, so you need to
change/add to the code a bit to make both events work. Something like this
perhaps:
Private Sub Worksheet_Change(ByVal Target As Range)
'Your current code about Data Validation goes here.
If Target.Column = 2 Then
If IsEmpty(Target.Offset(, -1).Value) Then
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
MsgBox "Some words that the date must be entered.", 16, "Date
Required"
End If
End If
End Sub
HTH Otto
"Keith" <(E-Mail Removed)> wrote in message
news:EC6BB1AD-4AA2-4174-8497-(E-Mail Removed)...
> Otto
> Thanks for your reply. I would think that the optiion of testing column B
> is
> by best option. Do you know of where I would put the code? I already have
> code for the Worksheet change event to decide what validation list to use
> based on another entry. Do I need to add it to this or is there a more
> specific event relating to the column?
>
> "Otto Moehrbach" wrote:
>
>> Keith
>> There are several ways you might approach this. Which way is best
>> depends on you and the specifics of your operation.
>> One way is to make a check (automatically) of all occupied rows (occupied
>> in
>> Column B?) at the time the file is saved. A message box could be used to
>> advise the user that he must enter a date in the occupied rows. The save
>> could be cancelled or not (your call) if dates are not there.
>> Another way might be to look (again automatically) at the date field for
>> a
>> date when any data is entered into Column B. A message box would tell
>> the
>> user whatever you want.
>> Still another way might be to automatically check the data at saving and
>> insert the previous date in any blank date cell that has data in Column
>> B.
>> Of course this assumes the previous date would be the correct date to
>> insert
>> into the blank cell.
>> Post back if any of this (or none of it) might work for you. HTH Otto
>> "Keith" <(E-Mail Removed)> wrote in message
>> news:A0075525-64C5-4787-ADD3-(E-Mail Removed)...
>> >I have an excel form that our sales team use out in the field. Each
>> >time
>> > they visit a client the need to fill in a row on the sheet.
>> >
>> > The first column is a date field. Starting at cell A4. Unfortunately
>> > they
>> > are in the habit of leaving this column blank on subsequent lines if
>> > the
>> > next
>> > visit was on the same day, so A4 could have 30/10/06 but A5 and A6
>> > might
>> > be
>> > blank with A7 being 31/10/06.
>> >
>> > Is there a way that I can force them to fill in column A for every
>> > entry?
>> >
>>
>>
>>
|
Similar Threads
