macro for filling in dates

[J_J]

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

[Carim]

Hi,

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

HTH
Carim

[Bob Phillips]

'-----------------------------------------------------------------
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Don Guillett]

right click sheet tab>view code>insert 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
[email protected]
"J_J" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[J_J]

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

"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> right click sheet tab>view code>insert 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
> [email protected]
> "J_J" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>