| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have Cell To Left Show Fixed Time When Cell Changes
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Have Cell To Left Show Fixed Time When Cell Changes
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 On Fri, 20 May 2011 16:45:41 -0700 (PDT), robzrob wrote: 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Have Cell To Left Show Fixed Time When Cell Changes
On May 21, 1:07*am, Gord Dibben wrote:
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 On Fri, 20 May 2011 16:45:41 -0700 (PDT), robzrob wrote: 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.- Hide quoted text - - 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Have Cell To Left Show Fixed Time When Cell Changes
Did you make sure to delete the original worksheet code?
Gord On Sat, 21 May 2011 02:24:18 -0700 (PDT), robzrob wrote: 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Have Cell To Left Show Fixed Time When Cell Changes
On May 21, 1:07*am, Gord Dibben wrote:
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 On Fri, 20 May 2011 16:45:41 -0700 (PDT), robzrob wrote: 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.- Hide quoted text - - Show quoted text - Retyped everything into that new workbook - now running - thanks very much. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Have Cell To Left Show Fixed Time When Cell Changes
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 -- isabelle Le 2011-05-20 19:45, robzrob a écrit : 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| show row number in one fixed cell in reference to your cursor pos. | Excel Discussion (Misc queries) | |||
| Show a Cell at Top Left | Excel Discussion (Misc queries) | |||
| top/left cell borders don't show | Excel Programming | |||
| enter a time into a cell, have the cell show two times the entry | Excel Worksheet Functions | |||
| Show a time from one calculated cell as a decimal in another cell. | Excel Worksheet Functions | |||
Hybrid Mode
