Auto Enter of Date if Columns *not* blank.

  • Thread starter Thread starter beeawwb
  • Start date Start date
B

beeawwb

Hello all.

Trying to work out an auto-enter problem, but not really sure how to
get started.

First, let me outline my spreadsheet.

There are 4 coulmns (ALL the other ones are hidden.)

Column A contains Text (the reference), Column B contains dollar values
(cheque amounts), Colum C contains a number (the number of invoices),
and column D contains a date (the date sent.)

Now, when I'm doing entries to this sheet, D will ALWAYS equal today's
date. So, I usually enter CTRL+; (semicolon) to enter the date.

What I want to do is this.

If A, B and C are not-blank (ie have values) and D IS blank, then when
I push "Enter" in Column D, it would enter just the value for today's
date (Ie, 18/02/2004) as a static value (so, it wouldn't ever change).


Any ideas? I assume it would need to be using an Intersect Range or
something like that in VBA, that would check the current column and
then check against the current row?

Many thanks for any help you can provide.

-Bob
 
Bob

One way is to use a worksheet change event. Right click on
the sheet name tab and select View code and Paste this in.

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
If Target.Column = 1 and target.row > 3 Then
ActiveCell.Offset(-1, 3) = Now()
End If
End Sub

This will enter a date when you move to the next line.
Beware, if you click in column A in old data then the date
will be updated.

Another is to use a customised function.

Public Function statdate()
'Dim myDate
Static myDate
myDate = Now()
statdate = myDate
End Function

Paste this into a module and type =STATDATE() to call it.
The function will not update if you press F9 but will if
you Press Ctrl + Alt + F9

Regards
Peter
 
First solution isnt really what I'm looking for as I highlight / move
between cells quite a bit, just to highlight data that I'm looking for.


Second solution is closer, except I have to manually update cells
anyway.

Oooh. Maybe a solution using the first...

What about something like (and this is psuedo-code only, I dont know if
it would work, or how to make it do so.)

Worksheet_Change
Static mydate
mydate = date
If Target.Column = 4 And Target.Row > 3 Then _
If AND(Target.Row:Column 1 <> "", Target.Row:Column 2 <>"",
Target.Row:column 3 <> "",Target.Cell="")
Target.Cell = mydate

Is that possible in real code?

-Bob
 
Hi
try the following

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:C")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
with target
If cells(.row,1).value<>"" and cells(.row,2).value <>"" and _
cells(.row,3).value <>"" and cells(.row,4).value ="" then
Application.EnableEvents = False
cells(.row,4).value = now
end if
end with
CleanUp:
Application.EnableEvents = True
End Sub


Note: Just moving between the cells does not change anything. I also
added the condition that if col. D was filled before a change in A-C
won't change it
 
Back
Top