Can I force an entry?

[Guest]

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?

 

[Otto Moehrbach]

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

 

[Guest]

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]

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

 

[Guest]

Thanks Otto

That is exactly what I am looking for.

Thanks again for thaking the time to help.

Keith

 

[Otto Moehrbach]

Keith
Glad to do it. Thanks for the feedback. Otto