Have Cell To Left Show Fixed Time When Cell Changes

  • Thread starter Thread starter robzrob
  • Start date Start date
R

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.
 
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
 
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
 
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 -
Click to expand...

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.
 
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 -
Click to expand...

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