Have Cell To Left Show Fixed Time When Cell Changes

[robzrob]

I've tried this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("A" & n).Text = "" And Target.Cells.Text <> ""
Then
Excel.Range("A" & n).Value = Now
End If
End If
End Sub

suitably amended, and some others, but can't seem to get them to work.

I want E7 to show time as soon as F7 is <>"", E8 to show time as soon
as F8 is <>"", etc - for any worksheet in the workbook, not just on
one worksheet.

 

[Gord Dibben]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Column = 6 Then
n = Target.Row
If Excel.Range("E" & n).Text = "" And Target.Cells.Text <> "" Then
Excel.Range("E" & n).Value = Format(Now, "hh:mm:ss AM/PM")
End If
End If
End Sub


This code is to be placed in Thisworkbook module........not a sheet module.

It will run on every sheet in the workbook.


Gord Dibben MS Excel MVP

 

[isabelle]

hi,

in vba (ThisWorkbook code), try event "Workbook_SheetChange"


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 2 Then
'...

End Sub

 

[robzrob]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Cells.Column = 6 Then
        n = Target.Row
        If Excel.Range("E" & n).Text = "" And Target.Cells.Text<> "" Then
            Excel.Range("E" & n).Value = Format(Now, "hh:mm:ss AM/PM")
        End If
    End If
End Sub

This code is to be placed in Thisworkbook module........not a sheet module.

It will run on every sheet in the workbook.

Gord Dibben     MS Excel MVP








- Show quoted text -

Tried this in the workbook - in ThisWorkbook - but it won't go - then
opened a new workbook and kept it blank and put the macro in that one
and it worked! Any idea what can be stopping it in my original
workbook? I haven't locked any cells, there's nothing else
controlling cols 5 & 6.

 

[robzrob]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Cells.Column = 6 Then
        n = Target.Row
        If Excel.Range("E" & n).Text = "" And Target.Cells.Text<> "" Then
            Excel.Range("E" & n).Value = Format(Now, "hh:mm:ss AM/PM")
        End If
    End If
End Sub

This code is to be placed in Thisworkbook module........not a sheet module.

It will run on every sheet in the workbook.

Gord Dibben     MS Excel MVP








- Show quoted text -

Retyped everything into that new workbook - now running - thanks very
much.

 

[Gord Dibben]

Did you make sure to delete the original worksheet code?


Gord