Home |
Search |
Today's Posts |
|
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Per,
Your point is very valid. (See the code in this thread to Dave Peterson) Thank you! "Per Jessen" wrote: Hi EagleOne As Target is already a range you can just use Set MyRange =Target, or just juse Target in your macro. From what you post I can not tell if IndexColumn is supposed to be a fixed value or subject to change. As your code verify that Target is just one cell, you do not need a For Each...Next loop. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then Application.EnableEvents = False With Cells(Target.Row, IndexColumn+1) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = Now() End With Application.EnableEvents = True End If End Sub Regards, Per skrev i meddelelsen .. . I thing I have it. The following works in that I am limiting the Date/Time to all changes where the cell count is 1. Anyone know where this is not appropriate or have a better approach? Set myRange = Range(Target.Address) If Target.Count = 1 Then For Each myCell In myRange With Me.Cells(myCell.Row, IndexColumn + 1) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell End If TIA EagleOne wrote: Hi, maybe this If Target.Column < 125 Then Exit Sub column 125 is "DU" so change to suit. Mike On Wed, 14 Oct 2009 08:22:50 -0400, wrote: 2003 2007 What would the VBA Syntax to eliminate (from ChangeEvent) i.e. the addition of a Column within the Target Range? The issue is that all cells in that column would be populated eith a time/date stamp. Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String myTime = Now() Set myRange = Range(Target.Address) With Application .EnableEvents = False .Screenupdating = False End with For Each myCell In myRange With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End with End Sub TIA EagleOne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Blocking column insertion | Excel Programming | |||
Custom Context menu (Right click menu) not working in sheet changeevent. | Excel Programming | |||
ChangeEvent | Excel Programming | |||
Create event triggered by Pivot Chart field button | Excel Programming | |||
Text following an Outlook field insertion | Excel Programming |