Hi experts,
I have a small macro problem here I need to solve but can't seem to find my
way out.
Here is the problem: On Sheet1 column A:A I need to write down names and
when I press enter after inputting the info, I want my macro to check if
column "I" on the same row is empty and if so fill in that days date (in the
"dd.mm.yyyy" format) to this cell on column "I", same row. But if there is
already a date there, the macro should ask me if I want to change that date
or not before doing so.
Is it a simple task?.
TIA
J_J

Hi,

take a look at Jim's brilliant solution :
http://www.mcgimpsey.com/excel/timestamp.html

HTH
Carim

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A:A"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Offset(0, 8).Value < "" Then
If MsgBox("Overwrite existing date?", vbYesNo) = vbYes Then
.Offset(0, 8).Value = Format(Date, "dd.mm.yyyy")
End If
Else
.Offset(0, 8).Value = Format(Date, "dd.mm.yyyy")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"J_J" wrote in message
...
Hi experts,
I have a small macro problem here I need to solve but can't seem to find
my
way out.
Here is the problem: On Sheet1 column A:A I need to write down names and
when I press enter after inputting the info, I want my macro to check if
column "I" on the same row is empty and if so fill in that days date (in
the
"dd.mm.yyyy" format) to this cell on column "I", same row. But if there is
already a date there, the macro should ask me if I want to change that
date
or not before doing so.
Is it a simple task?.
TIA
J_J

right click sheet tabview codeinsert this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dc As Range
If Target.Row < 2 And Target.Column < 1 Then Exit Sub
Set dc = Cells(Target.Row, "I")
Application.EnableEvents = False
If Not IsDate(dc) Then
dc = Date
Else
nd = InputBox("Change date or cancel")
If nd = "" Then
Application.EnableEvents = True
Exit Sub
Else
dc = nd
End If
End If
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software

"J_J" wrote in message
...
Hi experts,
I have a small macro problem here I need to solve but can't seem to find
my way out.
Here is the problem: On Sheet1 column A:A I need to write down names and
when I press enter after inputting the info, I want my macro to check if
column "I" on the same row is empty and if so fill in that days date (in
the "dd.mm.yyyy" format) to this cell on column "I", same row. But if
there is already a date there, the macro should ask me if I want to change
that date or not before doing so.
Is it a simple task?.
TIA
J_J

Thank you all...
I'll give them all a try.
J_J

"Don Guillett" wrote in message
...
right click sheet tabview codeinsert this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dc As Range
If Target.Row < 2 And Target.Column < 1 Then Exit Sub
Set dc = Cells(Target.Row, "I")
Application.EnableEvents = False
If Not IsDate(dc) Then
dc = Date
Else
nd = InputBox("Change date or cancel")
If nd = "" Then
Application.EnableEvents = True
Exit Sub
Else
dc = nd
End If
End If
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software

"J_J" wrote in message
...
Hi experts,
I have a small macro problem here I need to solve but can't seem to find
my way out.
Here is the problem: On Sheet1 column A:A I need to write down names and
when I press enter after inputting the info, I want my macro to check if
column "I" on the same row is empty and if so fill in that days date (in
the "dd.mm.yyyy" format) to this cell on column "I", same row. But if
there is already a date there, the macro should ask me if I want to
change that date or not before doing so.
Is it a simple task?.
TIA
J_J