Worksheet_SelectionChange code: lavish use of system resources?

[slingsh0t]

I wrote some Worksheet_SelectionChange code to augment a large workbook
in use by our company. The routines are not complicated, and just take
care of some routine housekeeping-type chores. The code runs when the
cell pointer lands in a certain cell range; when I explained this
functionality, the guy in charge of version control for this workbook
questioned whether the routines would sponge up excessive system
resources.

Do these routines take up extra processor time, or memory allocation,
or contribute substantially to "overhead" that may cause the
spreadsheet to operate more slowly than it might without the code?

Thanks

 

[Dave Peterson]

Do you turn off events when you do something so that your code won't cause a
change that would fire your code again (and again and again)?

If yes, then I'd say don't worry about the "overhead".

The only two complaints that I have with events is that sometimes the clipboard
is killed and sometimes it looks too much like magic!

 

[slingsh0t]

Dave-
Thanks for your response- how do I "turn off events"? I don't think
I'm at risk of repetitively triggering the code, but I'd like to know
so I can write better code.

Thanks!

LOL!

 

[Dave Peterson]

In this kind of code:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
Target.Offset(0, 1).Select
End If
End Sub

By selecting something in the code, it triggers the _selectionchange event
again.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
application.enableevents = false
Target.Offset(0, 1).Select
application.enableevents = true
End If
End Sub

You may find that you change something in the _change event.

This would put the time/date in column F if something changed in A:E:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:e")) Is Nothing Then
Exit Sub
End If

On Error GoTo errHandler:

With Me.Cells(Target.Row, "F")
Application.EnableEvents = False
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With

errHandler:
Application.EnableEvents = True
End Sub

 

[slingsh0t]

Thanks a million- I'll see how I can apply that to my code.