• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date and time insertion from data put in a cell

T

thesilkster

Member
Hello i would like the date and time inserted into a cell the same as below,, the code below populates the date and time when cell "S" has data entered into it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range

Set rInt = Intersect(Target, Range("R:R"))
If Not rInt Is Nothing Then
For Each rCell In rInt
Set tCell = rCell.Offset(0, -1)
If IsEmpty(tCell) Then
tCell = Now
tCell.NumberFormat = "mmm d, yyyy hh:mm"
End If
Next
End If
End Sub

what i need is another code that populates cell "B" with the time and date when data is entered into cell C ,is this possible? and will it still work if i lock the colums R & B

any help is appreciated
 
Hi ,

Just changing the following line of code :

Set rInt = Intersect(Target, Range("S:S"))

to :

Set rInt = Intersect(Target, Union(Range("C:C") , Range("S:S"))

should allow you to change data in column C or column S , and have the date and time entered in the corresponding cell in column B or column R.

Locking columns B and R and protecting the worksheet will not allow the date and time entry , unless you use the VBA protection method , whereby you can use the optional parameter UserInterfaceOnly.

Lookup the Excel VBA help on the Worksheet.Protect method.

Narayan
 
You must log in or register to reply here.
Back
Top