VB Code Required for Capturing Time

[TGV]

If i enter anything in B column cell then the vb code should caputure the
current system timing in C column cell. For example if i am entering a text
in B1 cell then the vb code should capture the current time in C1 cell. Like
this it should capture the time in C column for B Column entry, and this VB
Code should work for all the sheets in that workbook.

Thanks in Advance.

TGV

 

[TGV]

Hi,

I forgot to say one thing that is i want to protect the C column and rest of
the columns except B column whether the macro will work on protected columns?

People can able to access only the B Column so i dont know whether the macro
will generate the result in the protected cells. whether it is possible?

Thank you

TGV

 

[Mike H]

Hi,

Alt+F11 to open VB editor. Doubleclick 'ThisWorkbook' and paste the code in
on the right. Note if you want the dat and the time change TIME to NOW

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(, 1).Value = Time
Application.EnableEvents = True
End If
End Sub

Mike

 

[Mike H]

Hi,

Thats is an important detail you left out. For this to work column B cells
must be unlocked or data can't be entered in those cells and I assume you
know how to do that with

Format|Cells - protection tab and remove the 'Locked' checkmark for column B
cells.

To enter anything into column C it must be fleetingly unprotected and this
revised macro will do that. Change MyPass to your password.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="MyPass"
Target.Offset(, 1).Value = Time
ActiveSheet.Protect Password:="MyPass"
Application.EnableEvents = True
End If
End Sub

Mike

 

[TGV]

Thank you Boss it's very Nice........

TGV

Hi,

Thats is an important detail you left out. For this to work column B cells
must be unlocked or data can't be entered in those cells and I assume you
know how to do that with

Format|Cells - protection tab and remove the 'Locked' checkmark for column B
cells.

To enter anything into column C it must be fleetingly unprotected and this
revised macro will do that. Change MyPass to your password.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="MyPass"
Target.Offset(, 1).Value = Time
ActiveSheet.Protect Password:="MyPass"
Application.EnableEvents = True
End If
End Sub

Mike