Worksheet SelectionChange event

[richardbuttrey]

Hi, I have the following bit of code attached to a worksheet object:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next
If Target = Range("job_no") Then
Application.ScreenUpdating = False
Worksheets("summary").Calculate
Call ...MyCode.....
Application.ScreenUpdating = True
End If

End Sub

This works fine when the cell called "job_no" is selected or changed,
however it also runs when some other cells, (but not all), containing
just text, no formulae, are also changed.

Can anyone suggest what may be happening. I only want it to run if the
"job_no" cell is changed/selected.

Usual TIA

Rgds

 

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next
If Not Intersect(Target, Range("job_no")) Is Nothing Then
Application.ScreenUpdating = False
Worksheets("summary").Calculate
'Call ...MyCode.....
Application.ScreenUpdating = True
End If

End Sub

 

[keepITcool]

you want the CHANGE event not the SELECTIONCHANGE event.

note that if you are only monitoring certain cells
you should exit from the eventhandler as quickly as possible.

e.g. if target.count > 1 then exit sub


note that if your evetn handler (or procedures called from it)
make any changes to cells you should temporarily suspend event
monitoring to prevent looping.

application.enableevents = false
'make your changes
application.enableevents = true

 

[Richard Buttrey]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next
If Not Intersect(Target, Range("job_no")) Is Nothing Then
Application.ScreenUpdating = False
Worksheets("summary").Calculate
'Call ...MyCode.....
Application.ScreenUpdating = True
End If

End Sub

Many thanks Bob. That works fine.

So that I can better understand my original problem, what does the
'Not Intersect....Is nothing' do/mean in English?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Bob Phillips]

What it is doing is looking to see whether the selected range of cells (or
cell) is within a specified range. It does this by comparing the two range
objects using the intersect method, which returns a Range object that
represents the rectangular intersection of the two ranges. SO, if they do
intersect, the returned range object will represent a range of cells on the
worksheet, that is Not Nothing, but if they don't, it will not, that is
Nothing.

Does that make sense?

BTW is Grappenhall anywhere near Jodrell Bank, and Twemlow?

 

[Richard Buttrey]

What it is doing is looking to see whether the selected range of cells (or
cell) is within a specified range. It does this by comparing the two range
objects using the intersect method, which returns a Range object that
represents the rectangular intersection of the two ranges. SO, if they do
intersect, the returned range object will represent a range of cells on the
worksheet, that is Not Nothing, but if they don't, it will not, that is
Nothing.

Does that make sense?

Indeed it does. Thanks

BTW is Grappenhall anywhere near Jodrell Bank, and Twemlow?

It is indeed, well within 15 miles or so. The Jodrell Bank telescope
can be seen quite easily from the higher ground just outside
Grappenhall village.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Bob Phillips]

It is indeed, well within 15 miles or so. The Jodrell Bank telescope
can be seen quite easily from the higher ground just outside
Grappenhall village.

Just wondered as I used to go to Knutsford a lot, and would visit the
villages around Jodrell. Some of the roads in our area are named after those
villages.