Hi,

I am trying to place current date in a particular cell in the excel sheet using the below vba code. The code will activate once I made changes in B10 Cell.

=IF(LEN(TRIM(B10))=0,"",TODAY())

It is working fine.

My problem is when I am opening the same sheet at next day, the date is displaying as CURRENT date because I used TODAY() function in the code.

I need once it is placed the transaction date, should keep the same date later on.

So what could be the funcion other that TODAY() in the above code?

Please advise.

Regards,
Ashfaque

Are you using this as a formula in the cell? If so, then you should use VBA and run it off of an event or command button to test the value of B10 and only update the cell if it meets the criteria and only run when you run it.

Hi, Ashfaque,

if it´s entered by the user you could use the following code which will check for any change in B10 (code goes behind the sheet where the action should take place):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B10")) Is Nothing Then
Application.EnableEvents = False
If Len(Trim(Range("B10"))) > 0 Then
Range("C10").Value = Date
Else
Range("C10").Value = vbNullString
End If
Application.EnableEvents = True
End If
End Sub
If a formula is in cell B10 the Worksheet_Calculate-event must be used instead.

Ciao,
Holger

Hi Gents,

The subjected formula is in let us say C10 cell and the user is updating in B10. Then what I need is the date in C10 should be the date when action taken. And when the sheet opened next day or any other day, the date in C10 should display the old date that was stored. And this I need to place in many cells downwords like.

B10 -----C10
B11 -----C11
B12 -----C12
likewise...

Your code may be right but how can I use that?

Please advise.

With kind regards,
Ashfaque

Hi, Ashfaque,

set the security level for Excel to medium and allow macros when opening the workbook.

Please use this altered code which should do fair enough for checking in column B from row 10 on. Right-Click the WorkbookTab and paste the code in there, it will only be available in the sheet where it´s placed:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row < 10 Then Exit Sub
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
Application.EnableEvents = False
If Len(Trim(Target)) > 0 Then
Target.Offset(0, 1).Value = Date
Else
Target.Offset(0, 1).Value = vbNullString
End If
Application.EnableEvents = True
End If
End Sub
Ciao,
Holger

Excellent.....

Thanks Ciao,

Interesting code....

Thanks a lot.

WIth kind regards,
Ashfaque Hussain