Worksheet SelectionChange event

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

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


--
HTH

Bob Phillips

<[email protected]> wrote in message
news:[email protected]...
> 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
>

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






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


[email protected] wrote :

> 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

On Wed, 22 Jun 2005 11:36:14 +0100, "Bob Phillips"
<[email protected]> wrote:

>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
__________________________

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?

--
HTH

Bob Phillips

"Richard Buttrey" <[email protected]> wrote in
message news:[email protected]...
>
>
> 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
> __________________________

On Wed, 22 Jun 2005 23:39:41 +0100, "Bob Phillips"
<[email protected]> wrote:

>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
__________________________

"Richard Buttrey" <[email protected]> wrote in
message news:[email protected]...
> On Wed, 22 Jun 2005 23:39:41 +0100, "Bob Phillips"
> <[email protected]> wrote:
>
> >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.
>

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.