worksheet change

[gbell3587]

Hi

I know about the worksheet events but i have a whole range of cells that this would apply to and im not sure how i would do it.

Basically i want to date changes so if the user edits the data in a1, i want b1 to date the last time the cell a1 was changed. I have data in the whole of column a adn would also like the whole of column b to record any time the given row in column a changes.

Can anyone help?

as another example, i change cell a1 now - i want b1 to store todays date.

if i change cell a5000 on the 1/5/2011, i want this date to be displayed in cell b5000.

Thanks
Graham

 

[Jonmo1]

Look at the Intersect Method..

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, c As Range
Set MyRange = Intersect(Target, Range("A:A"))
If MyRange Is Nothing Then Exit Sub
For Each c In MyRange
    c.Offset(0, 1).Value = Date
Next c
End Sub

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = Now
    Application.EnableEvents = True
End If
End Sub

 

[gbell3587]

Look at the Intersect Method..

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, c As Range
Set MyRange = Intersect(Target, Range("A:A"))
If MyRange Is Nothing Then Exit Sub
For Each c In MyRange
    c.Offset(0, 1).Value = Date
Next c
End Sub

Thanks alot, much appreciated - you guys on this forum are unreal at helping quickly!

Ive never seen the intersect method before - i'll have to look into it. Just 1 more question, is this a static date? It isnt like the =now() function?

 

[Jonmo1]

At the time the code runs, Date is whatever today's current date is.

Then, the date that was put in the cell remains static After that point.

 

[gbell3587]

At the time the code runs, Date is whatever today's current date is.

Then, the date that was put in the cell remains static After that point.

Brilliant, exactly what im after.

 

[CPerdue]

@VoG, That Application.EnableEvents trick is nice too. I've been burned by large paste/format operations into sheets with a Worksheet_change event defined. Are there other change events, for a range perhaps, or do you have to resort to a data entry box if you need an event and can't tolerate a Worksheet_change?

 

[VoG]

If I understand your question you can use the Intersect method as in jonmo1's code above.

 

[CPerdue]

Not really - problem was the Worksheet_change reacting a bazillion times to things I didn't care about. Testing and exiting the macro still cost time. I want to avoid reacting at all to changes in/outside a defined area. I'll disable the event when I know a big change is coming but if a user pastes a few thousand cells...

 

[VoG]

I think you'll need to be more specific. Better still, start your own thread as yours seems to be a different question.